Hi, I hope some of you excel gurus are still around as I have a further complication to add to this formula: =IF(OR(A3="",B3=""),"",IF(NETWORKDAYS(A3,B3,$AA$1:$AA$5)-1=0,HOUR(MOD(B3-A3,1))&" Hour "&MINUTE(MOD(B3-A3,1))&" Minutes",NETWORKDAYS(A3,B3,$AA$1:$AA$5)-1&" Days "&HOUR(MOD(B3-A3,1))&" Hour "&MINUTE(MOD(B3-A3,1))&" Minutes"))

which by the way I love and so did my Boss until a few errors popped in, and in errors I don't mean with the formula its with the working hours... we start at 09:00 and we finish at 16:30 we don't count weekends or other public holidays so the above formula works great but if I get a request in at 16:00 on a Monday with a reply needed by 12:00 the following day I get a return of 1 days and 20 hour.

Firstly is there a way to calculate actual working days and hours between the 2 time stamps,

Secondly is there a way of formatting the plural and singular of "Day" for 1 Day and "Days" for more than 1 and same with Hours and minutes,

For me this seems near impossible but you guys seem to know what your doing,

Tom

✔ Best Answer

OK, try this, it's a modification of the formula at the Pearson site. Start Date & Time in A1

End Date & Time in B1Holidays in AA1:AA5

Work Day Starts in AB1 = 09:00

Work Day Ends in AB2 = 16:30In cell C1 enter the formula:

=IF(AND(INT(A1)=INT(B1),NOT(ISNA(MATCH(INT(A1),$AA$1:$AA$5,0)))),"0 days 0 hours", IF(INT(A1)=INT(B1),"0 days " & ROUND(24*(B1-A1),2)&" hours",MAX(NETWORKDAYS(A1+1,B1-1,$AA$1:$AA$5),0)+INT(24*(((B1-INT(B1))-(A1-INT(A1)))+($AB$2-$AB$1))/(24*($AB$2-$AB$1)))&" days "&MOD(ROUND(((24*(B1-INT(B1)))-24*$AB$1)+(24*$AB$2-(24*(A1-INT(A1)))),2),ROUND((24*($AB$2-$AB$1)),2))&" hours "))

It is very long, so best to Copy & Paste from here.

These are the result I get with your data:

A B C 1) 10/14/2014 09:00 10/16/2014 16:30 3 days 0 hours 2) 10/14/2014 09:00 10/14/2014 16:30 0 days 7.5 hours 3) 10/14/2014 16:00 10/15/2014 12:30 0 days 4 hours 4) 10/24/2014 09:00 10/26/2014 11:00 1 days 2 hoursRemember, the results are Work Days, from 9:00 to 16:30

not 24 hour days.MIKE

I can't remember what the full requirements for this formula were, but you can try this: =IF(OR(A3="",B3=""),"",IF(NETWORKDAYS(A3,B3,$AA$1:$AA$5)-2=0,HOUR(MOD(B3-A3,1))&" Hour "&MINUTE(MOD(B3-A3,1))&" Minutes",NETWORKDAYS(A3,B3,$AA$1:$AA$5)-2&" Days "&HOUR(MOD(B3-A3,1))&" Hour "&MINUTE(MOD(B3-A3,1))&" Minutes"))

Same formula, I simply subtracted 2 days from the NETWORKDAYS() instead of one.

As for Day & Days it could probably be done, but the formula will be at least twice as long

MIKE

Here is the same formula as I posted above, but using Day & Days:

~~=IF(OR(A3="",B3=""),"",IF(NETWORKDAYS(A3,B3,$AA$1:$AA$5)-2=0,HOUR(MOD(B3-A3,1))&" Hour "&MINUTE(MOD(B3-A3,1))&" Minutes",IF(NETWORKDAYS(A3,B3,$AA$1:$AA$5)-2>1,NETWORKDAYS(A3,B3,$AA$1:$AA$5)-2&" Days "&HOUR(MOD(B3-A3,1))&" Hour "&MINUTE(MOD(B3-A3,1))&" Minutes",NETWORKDAYS(A3,B3,$AA$1:$AA$5)-2&" Day "&HOUR(MOD(B3-A3,1))&" Hour "&MINUTE(MOD(B3-A3,1))&" Minutes")))~~Edit Added:

Disregard this post, see next message.

MIKE

message edited by mmcconaghy

Disregard the last two, try this one and see if it does what you want: =IF(OR(A3="",B3=""),"",IF(INT(B3-A3)=0,HOUR(MOD(B3-A3,1))&" Hour "&MINUTE(MOD(B3-A3,1))&" Minutes",IF(NETWORKDAYS(A3,B3,$AA$1:$AA$5)-1=1,NETWORKDAYS(A3,B3,$AA$1:$AA$5)-1&" Day "&HOUR(MOD(B3-A3,1))&" Hour "&MINUTE(MOD(B3-A3,1))&" Minutes",NETWORKDAYS(A3,B3,$AA$1:$AA$5)-1&" Days "&HOUR(MOD(B3-A3,1))&" Hour "&MINUTE(MOD(B3-A3,1))&" Minutes")))

MIKE

Mike, That works better than the others,

Now where in that formula can I subtract the 16.5 hours from the end of one day to the start of the next working day?

Tom

Now where in that formula can I subtract the 16.5 hours from the end of one day to the start of the next working day?Sorry, don't understand what it is you looking for?

MIKE

Mike, Sorry if i'm not being clear of course in my head it makes sense,

Basically people get a task in work and are given a deadline for completion the formula I'm looking for is to calculate the total "working hours and minutes" taking into account that work starts at 09:00 and ends at 16:30 so i don't need to include to 16.5hr gap in my result

I tried this adjustment:

=IF(OR(A3="",B3=""),"",IF(INT(B3-A3)=0,HOUR(MOD(B3-A3,1))-17&" Hours "&MINUTE(MOD(B3-A3,1))+30&" Minutes",IF(NETWORKDAYS(A3,B3,$AA$1:$AA$5)-1=1,NETWORKDAYS(A3,B3,$AA$1:$AA$5)-1&" Day "&HOUR(MOD(B3-A3,1))&" Hour "&MINUTE(MOD(B3-A3,1))&" Minutes",NETWORKDAYS(A3,B3,$AA$1:$AA$5)-1&" Days "&HOUR(MOD(B3-A3,1))&" Hour "&MINUTE(MOD(B3-A3,1))&" Minutes")))

The problem is it works for certain calculations but others it goes over the 60mins

Also the result is still showing up "0 Days"

Tom

Please post some examples of what you have on your work sheet.

Use Column Letters and Row Numbers.

Read this HOW-TO, which explains the use of the PRE tags:http://www.computing.net/howtos/sho...

Make sure you give a sample that shows "0 Days"

Indicate what you want, as opposed to what it shows.

MIKE

If you want to give it a try on your own, try this site: http://www.cpearson.com/excel/DateT...

It show how to accomplish what you want.

"Working Days And Hours Between Two Dates And Times"

MIKE

Start End Mike's Formula My Adjustment Actual Time Lapst 14/10/2014 09:00 16/10/2014 16:30 2 Days 7 Hours 30 Minutes 2 Days 7 Hour 30 Minutes 2 Days 7 Hour 30 Minutes 14/10/2014 09:00 14/10/2014 16:30 7 Hours 30 Minutes -10 Hours 60 Minutes 7 Hours 30 Minutes 14/10/2014 16:00 15/10/2014 12:30 1 Days 20 Hours 30 Minutes 3 Hours 60 Minutes 4 Hours 24/10/2014 09:00 26/10/2014 11:00 2 Hours 0 Minutes 0 Days 2 Hour 0 MinutesSorry about the layout for some reason i can not get it right

Basically your Formula works when calculating a time difference within a working day and over a day but does not work if the start time is 16:00 and the finish is 12:30 the next day wheras my adjustment says 3hrs and 60mins technically correct but at the same time messy when adding up all the time spent doing the tasks

maybe this just can't be done

Tom

message edited by Tjreddy32

OK, try this, it's a modification of the formula at the Pearson site. Start Date & Time in A1

End Date & Time in B1Holidays in AA1:AA5

Work Day Starts in AB1 = 09:00

Work Day Ends in AB2 = 16:30In cell C1 enter the formula:

=IF(AND(INT(A1)=INT(B1),NOT(ISNA(MATCH(INT(A1),$AA$1:$AA$5,0)))),"0 days 0 hours", IF(INT(A1)=INT(B1),"0 days " & ROUND(24*(B1-A1),2)&" hours",MAX(NETWORKDAYS(A1+1,B1-1,$AA$1:$AA$5),0)+INT(24*(((B1-INT(B1))-(A1-INT(A1)))+($AB$2-$AB$1))/(24*($AB$2-$AB$1)))&" days "&MOD(ROUND(((24*(B1-INT(B1)))-24*$AB$1)+(24*$AB$2-(24*(A1-INT(A1)))),2),ROUND((24*($AB$2-$AB$1)),2))&" hours "))

It is very long, so best to Copy & Paste from here.

These are the result I get with your data:

A B C 1) 10/14/2014 09:00 10/16/2014 16:30 3 days 0 hours 2) 10/14/2014 09:00 10/14/2014 16:30 0 days 7.5 hours 3) 10/14/2014 16:00 10/15/2014 12:30 0 days 4 hours 4) 10/24/2014 09:00 10/26/2014 11:00 1 days 2 hoursRemember, the results are Work Days, from 9:00 to 16:30

not 24 hour days.MIKE

Ask Your Question

Weekly Poll

Do you trust smart speakers to not spy on you?

Discuss in The Lounge

Poll History