# 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 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 A1End Date & Time in B1Holidays in AA1:AA5Work 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 hours ```Remember, the results are Work Days, from 9:00 to 16:30not 24 hour days.MIKEhttp://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 longMIKEhttp://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.MIKEmessage 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")))MIKEhttp://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?MIKEhttp://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 resultI 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.MIKEhttp://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"MIKEhttp://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 rightBasically 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 doneTom message edited by Tjreddy32

Report •

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

Report •

#11 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 A1End Date & Time in B1Holidays in AA1:AA5Work 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 hours ```Remember, the results are Work Days, from 9:00 to 16:30not 24 hour days.MIKEhttp://www.skeptic.com/ 