Solved Counting hours not worked between two dates

June 12, 2015 at 05:31:55
Specs: Windows XP
Hi all, appreciate if you would help me on this.


A1 6/11/2015 18:00
A2 6/12/2015 10:00

Working hours 08:00 to 19:00

I want times not worked (Weekend included)

From the cell above, I wanted it to count from 6/11/15 08:00 to 18:00 = 10 hrs
6/12/15 10:00 to 19:00 = 9 hrs

So the formula should spit 19 hours not worked (Hours worked is 3 hours)

Also includes weekend (no need to use NETWORKDAYS)

Thanks..


See More: Counting hours not worked between two dates

Report •

✔ Best Answer
June 14, 2015 at 06:09:17
OK,

Try this:

=(A1-(INT(A1)+"08:00"))+((INT(A2)+"19:00")-A2)

Was getting, what you refer to as, UpTime & DownTIme confused.
See how that works.

EDIT:
Modified formula so it is shorter and times are hard coded.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy



#1
June 12, 2015 at 06:11:14
Sorry, I don't quite understand what your doing.

Your Work Day Starts at 8:00
Your Work Day Ends at 19:00

Correct?

So: 6/11/15 08:00 to 18:00 = 10 hrs
is how many hours worked?

But: 6/12/15 10:00 to 19:00 = 9 hrs
is this a start time of 10:00?

Your Dates & TImes are confusing.

MIKE

http://www.skeptic.com/


Report •

#2
June 12, 2015 at 06:36:26
What I meant is NOT worked..

Report •

#3
June 12, 2015 at 06:52:57
I still don't understand what your Dates & Times mean.

With no labels, they are simply dates and times with no context.

What relationship do they have with each other.

I can't see what your spreadsheet looks like from where I'm sitting
so you need to be very specific in explaining what you want.

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
June 12, 2015 at 07:13:09
Sorry, let me clarify with another context.

The context is service uptime/downtime within working hours of 08:00 to 19:00

A1 is down (meaning the service down at 18:00 on 6/11/15)

A2 is restored (meaning the service restored at 10:00 on 6/12/15) (next day)

What I want to find out is the service uptime within the 2 days.

So, on the 6/11/15 the UPTIME is from 08:00 to 18:00 which means 10 hours.

On 6/12/15 the UPTIME is from 10:00 to 19:00 which means 9 hours.

On both days, the service UPTIME would be a total of 19 hours while the downtime would be 3 hours of a total of 22 hours of working time in 2 days.

So what I really want is the service UPTIME.

Thanks..


Report •

#5
June 12, 2015 at 07:55:04
OK, I think I get what your looking for,

Could you give a few more examples to work with so I can cover all possibilities.

Can the Down & Restore be in a single day?
What the longest interval between Down & Restore?
The shortest interval?

           A                 B
1)   Service Down      Service Restored:
2) 6/11/2015 18:00     6/12/2015 10:00
3)
4)
5)

In your first post you mentioned:

Also includes weekend (no need to use NETWORKDAYS)

Not sure what you mean by this?

EDIT:

I get it, you do not need to exclude weekends.
A bit slow today.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#6
June 12, 2015 at 09:55:06
For the example given:
     A              B                 C        D
1) Down	     06/11/2015 18:00     Day Start  08:00
2) Restore   06/12/2015 10:00     Day End    19:00
3)

In cell B3 enter the formula:

=(B1-(INT(B1)+D1))+((INT(B2)+D2)-B2)

This does not take into account if the down time exceeds one day.

MIKE

http://www.skeptic.com/


Report •

#7
June 12, 2015 at 10:45:26
This formula should solve the one day limit:

=IF(DATEDIF(B1,B2,"D")<=1,(B1-(INT(B1)+D1))+((INT(B2)+D2)-B2),(B1-(INT(B1)+D1))+((INT(B2)+D2)-B2)+((DATEDIF(B1,B2,"D")*11)-11)/24)

MIKE

http://www.skeptic.com/


Report •

#8
June 12, 2015 at 16:37:28
Mike, I already found a formula to count the downtime but I cannot seem to modify it to calculate the uptime instead. Can you modify the below formula?

=(INT(A2)-INT(A1))*("19:00"-"8:00")+MEDIAN(MOD(A2;1);"8:00";"19:00")-MEDIAN(MOD(A1;1);"8:00";"19:00")

You see, the formula only use A1 & A2 cell only

Appreciate your help man..


Report •

#9
June 12, 2015 at 19:24:28
This is the same formula I posted earlier, but it uses
Cells A1 & A2 and C1 & C2 for your Day Start and Day End times.

            A           B         C
1) 06/11/2015 18:00  Day Start  08:00
2) 06/12/2015 10:00  Day End    19:00
3)

=IF(DATEDIF(A1,A2,"D")<=1,(A1-(INT(A1)+C1))+((INT(A2)+C2)-A2),(A1-(INT(A1)+C1))+((INT(A2)+C2)-A2)+((DATEDIF(A1,A2,"D")*11)-11)/24)

If you want your Day Start & Day End in different cells,
like AC1 & AC2, just change the corresponding cells in the formula.

MIKE

http://www.skeptic.com/


Report •

#10
June 12, 2015 at 19:32:24
I don't need to change day start or day end. It's a fixed value. What I mean is day start or day end is not a variable. Like the formula I've posted earlier.

Your formula seems to produce an error though..


Report •

#11
June 12, 2015 at 19:59:59
In your formula:

=(INT(A2)-INT(A1))*("19:00"-"8:00")+MEDIAN(MOD(A2;1);"8:00";"19:00")-MEDIAN(MOD(A1;1);"8:00";"19:00")

The Day Start and Day End times are Hard coded into the formula

In my formula

=IF(DATEDIF(A1,A2,"D")<=1,(A1-(INT(A1)+C1))+((INT(A2)+C2)-A2),(A1-(INT(A1)+C1))+((INT(A2)+C2)-A2)+((DATEDIF(A1,A2,"D")*11)-11)/24)

The Day Start and Day End times are placed in a cell, so that in the future,
if needed, the times can be modified without having to change any formula.

If you want the Day Start and Day End hard coded then try this:

=IF(DATEDIF(A1,A2,"D")<=1,(A1-(INT(A1)+"08:00"))+((INT(A2)+"19:00")-A2),(A1-(INT(A1)+"08:00"))+((INT(A2)+"19:00")-A2)+((DATEDIF(A1,A2,"D")*11)-11)/24)

MIKE

http://www.skeptic.com/


Report •

#12
June 12, 2015 at 20:43:25
Exactly. That's the word. I do want it hard coded. Your formula it works if it's within two days but somehow broke if beyond that, such as 3 days or more. Damn the one I gave you also broke.. now I'm dumbfounded..

Report •

#13
June 13, 2015 at 07:24:49
What are the error messages you get, if any.

My best guess, since we are dealing with Dates, is that the Date in one of the cells, is NOT a Date but a Text string that LOOKS like a Date.

Select both date cells, A1 & A2, and change the Format to Numbers, both cells should display a number similar to:

42166.75000
42167.41667

That is because, Excel stores Dates as a number representing the number of days since 1900-Jan-0, and Time as a fractional portion of a 24 hour day.
It is commonly refereed to as a serial date, or serial date-time.

Your formula it works if it's within two days but somehow broke if beyond that, such as 3 days or more.

I have tested the below formula (Start Day & End hardcoded) out to five days, and it seems to work for me.

=IF(DATEDIF(A1,A2,"D")<=1,(A1-(INT(A1)+"08:00"))+((INT(A2)+"19:00")-A2),(A1-(INT(A1)+"08:00"))+((INT(A2)+"19:00")-A2)+((DATEDIF(A1,A2,"D")*11)-11)/24)

Using the dates:

              A
1) 06/11/2015 18:00
2) 06/13/2015 10:00

I get 30:00:00

Using the dates:

              A
1) 06/11/2015 18:00
2) 06/14/2015 10:00

I get 41:00:00

Using the dates:

              A
1) 06/11/2015 18:00
2) 06/15/2015 10:00

I get 52:00:00

Using the dates:

              A
1) 06/11/2015 18:00
2) 06/16/2015 10:00

I get 63:00:00

MIKE

http://www.skeptic.com/


Report •

#14
June 13, 2015 at 21:22:19
That's the problem bro. No error, just the wrong result.

A
1) 06/11/2015 18:00
2) 06/13/2015 10:00

I get 30:00:00

U see as the above example, let me break down the problem.

On 6/11/15 down@18:00pm Uptime is 10 hrs
On 6/12/15 all day down so no uptime
On 6/13/15 up@10:00am uptime is 9 hrs.

So u see the formula should return 19 hrs since no uptime on the 2nd day.


Report •

#15
June 14, 2015 at 06:09:17
✔ Best Answer
OK,

Try this:

=(A1-(INT(A1)+"08:00"))+((INT(A2)+"19:00")-A2)

Was getting, what you refer to as, UpTime & DownTIme confused.
See how that works.

EDIT:
Modified formula so it is shorter and times are hard coded.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#16
June 15, 2015 at 04:45:45
Works great bro. However my xxx of a boss now wants the downtime. I tried to modify it myself but gave wrong results everytime. Could u do me another favor, another formula for downtime?

Your formula I guessed it's easier for u to change it back. Math is definitely not my forte..


Report •

#17
June 15, 2015 at 05:24:15
In reply #8 you stated

I already found a formula to count the downtime

and you posted this formula:

=(INT(A2)-INT(A1))*("19:00"-"8:00")+MEDIAN(MOD(A2;1);"8:00";"19:00")-MEDIAN(MOD(A1;1);"8:00";"19:00")

Does it not work?

I tested it out and seems to work OK for me.

MIKE

http://www.skeptic.com/


Report •

#18
June 15, 2015 at 09:17:22
First 3 days works fine, on the 4th day onward gave crappy result..

Report •

#19
June 15, 2015 at 09:53:45
Tested it out to 7 days.
Are these numbers correct or am I missing something again.

            A                B     
 1) 06/11/2015 18:00              
 2) 06/12/2015 10:00      3:00:00
 3)
 4) 06/11/2015 18:00      
 5) 06/13/2015 10:00     14:00:00
 6)
 7) 06/11/2015 18:00      
 8) 06/14/2015 10:00     25:00:00
 9)
10) 06/11/2015 18:00     
11) 06/15/2015 10:00     36:00:00
12)
13) 06/11/2015 18:00     
14) 06/16/2015 10:00     47:00:00
15)
16) 06/11/2015 18:00     
17) 06/17/2015 10:00     58:00:00
18)
19) 06/11/2015 18:00     
20) 06/18/2015 10:00     69:00:00
21)
22) 06/11/2015 18:00     
23) 06/19/2015 10:00     80:00:00

MIKE

http://www.skeptic.com/


Report •

#20
June 15, 2015 at 18:25:53
Ohh snap, I used h:mm instead of [h]:mm. Man, you've been a great help. I appreciate your time man. Hats off to you!

Report •

#21
June 15, 2015 at 19:02:37
Glad you got it working.

MIKE

http://www.skeptic.com/


Report •

Ask Question