• 460K Members
• 9,558 Online
• 557K Conversations

Deleted
Not applicable

# 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

# Re: Calculating and expressing time worked as days hours minutes

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

# Re: Calculating and expressing time worked as days hours minutes

Thanks very much for this, if I embed network days I should be able to make it work

# Re: Calculating and expressing time worked as days hours minutes

I can't do it..  how do I embed the networking days and the holidays into the suggested formula

=INT(J2-E2)&"days"&TEXT(J2-E2,"h""hrs""m""mins""")  ?

# Re: Calculating and expressing time worked as days hours minutes

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.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies