Formula question - End date falls on the weekend but start date on weekday

[Li_zzza]

Hi everyone,


I'm currently working on a project and need to calculate the working hours between two dates. The project working hours are usually M-F, however, sometimes the dates fall on the weekend. The Networkdays.intl formula works perfectly when the start date and end date for weekdays. But for end dates or start dates on the weekend, it gives me a bunch of pound signs.

How do I make it that if the end date ends on the weekend, to only include working hours? For example, start date is Friday, 13.4.17 at 13:00 and end date is Saturday, 14.4.17 at 11:00. So the total working hours is 11 hours not 22 hours.

Current formula is Networkdays.intl(Start date, end date,,Holidays)-1-Mod(Start date,1)+Mod(end date, 1)
View format is [h];mm;@

Thanks

 

[barry houdini]

Hello Li_zzza, welcome to MrExcel

You can use this formula to only give weekday hours

=NETWORKDAYS(StartDate,EndDate,Holidays)+NETWORKDAYS(EndDate,EndDate,Holidays)*(MOD(EndDate,1)-1)-NETWORKDAYS(StartDate,StartDate,Holidays)*MOD(StartDate,1)

 

[Li_zzza]

Thank you so much!! It works perfectly now!!