Forum Discussion
Calculating and expressing time worked as days hours minutes
I have issue opened 29/10/2018 10:15 and issue closed 04/11/2018 12:00
I want to express time worked on the issue as 3 days 5 hours 5 minutes
I don't want to count time outside 5pm or before 9am or weekends or bank holidays
I have this but it doesn't do the days bit for me, it's just hours
=IF(OR($B$2<$A$2,B5<A5),0,(NETWORKDAYS(A5,B5,HolidayList)-(NETWORKDAYS(A5,A5,HolidayList)*IF(MOD(A5,1)>$B$2,1,(MAX($A$2,MOD(A5,1))-$A$2)/($B$2-$A$2)))-(NETWORKDAYS(B5,B5,HolidayList)*IF(MOD(B5,1)<$A$2,1,($B$2-MIN($B$2,MOD(B5,1)))/($B$2-$A$2))))*($B$2-$A$2)*24)
Where A2 = 9am
B2= 5pm
A6 = 29/10/2018 10:15
B6 = 04/11/2018 12:00
I have a separate worksheet with bank holidays on
the calculation is working it's just the expression I can't get
4 Replies
Hi Georgie,
General idea is here https://exceljet.net/formula/get-days-hours-and-minutes-between-dates. To suggest concrete formula for your case could you please clarify how did you receive 3 days 5 hours 5 minutes in your sample - you have 4 workdays in between plus 06:45 on first day and 3 hours of work time on last day
As variant
=NETWORKDAYS(A5+1,B5-1,HolidayList) & " days " & TEXT(B2-(A5-INT(A5))+B5-INT(B5)-A2,"h"" hours ""m""minutes""")
but shall be modified if hours in first and last days are more than 8 hrs in sum and you'd like to consider them as 1 day plus some time, not sure about business logic here. Please see attached.
