Hi all, appreciate if you would help me on this.

A1 6/11/2015 18:00

A2 6/12/2015 10:00Working 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 hrsSo the formula should spit 19 hours not worked (Hours worked is 3 hours)

Also includes weekend (no need to use NETWORKDAYS)

Thanks..

✔ 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

message edited by mmcconaghy

Sorry, I don't quite understand what your doing. Your Work Day Starts at 8:00

Your Work Day Ends at 19:00Correct?

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

What I meant is NOT worked..

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

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

uptimewithin 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..

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

message edited by mmcconaghy

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

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

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..

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

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..

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 codedinto the formulaIn 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

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..

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.41667That 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:00I get 30:00:00

Using the dates:

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

Using the dates:

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

Using the dates:

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

MIKE

That's the problem bro. No error, just the wrong result. A

1) 06/11/2015 18:00

2) 06/13/2015 10:00I 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.

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

message edited by mmcconaghy

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..

In reply #8 you stated

I already found a formula to count the downtimeand 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

First 3 days works fine, on the 4th day onward gave crappy result..

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:00MIKE

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!

Ask Your Question

Weekly Poll

Do you trust smart speakers to not spy on you?

Discuss in The Lounge

Poll History