Solved Calculating exact time between two time stamps

October 7, 2014 at 12:43:37
Specs: Windows 7
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


See More: Calculating exact time between two time stamps

Report •

✔ Best Answer
October 7, 2014 at 20:59:00
OK, try this, it's a modification of the formula at the Pearson site.

Start Date & Time in A1
End Date & Time in B1

Holidays in AA1:AA5
Work Day Starts in AB1 = 09:00
Work Day Ends in AB2 = 16:30

In 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 hours 

Remember, the results are Work Days, from 9:00 to 16:30
not 24 hour days.

MIKE

http://www.skeptic.com/



#1
October 7, 2014 at 13:56:34
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

http://www.skeptic.com/


Report •

#2
October 7, 2014 at 14:13:00
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

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#3
October 7, 2014 at 14:30:20
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

http://www.skeptic.com/


Report •

Related Solutions

#4
October 7, 2014 at 15:38:57
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


Report •

#5
October 7, 2014 at 15:41:14
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

http://www.skeptic.com/


Report •

#6
October 7, 2014 at 16:20:20
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


Report •

#7
October 7, 2014 at 16:47:17
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

http://www.skeptic.com/


Report •

#8
October 7, 2014 at 17:08:53
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

http://www.skeptic.com/


Report •

#9
October 7, 2014 at 17:34:27

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 Minutes	

Sorry 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


Report •

#10
October 7, 2014 at 19:20:55
Disregard, see next message.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#11
October 7, 2014 at 20:59:00
✔ 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 B1

Holidays in AA1:AA5
Work Day Starts in AB1 = 09:00
Work Day Ends in AB2 = 16:30

In 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 hours 

Remember, the results are Work Days, from 9:00 to 16:30
not 24 hour days.

MIKE

http://www.skeptic.com/


Report •

Ask Question