Forum Discussion
Calculate days and hr min based on start and end date in SP
bbsin your first request to only display hours and minutes can be done with
=IF(ISBLANK(End),"",INT((End-Start)*1440)-MOD(INT((End-Start)*1440),60))/60&" Hours "&MOD(INT((End-Start)*1440),60)&" Minutes"Rob
Los Gallardos
Microsoft Power Automate Community Super User.
Principal Consultant, SharePoint and Power Platform WSP (and classic 1967 Morris Traveller driver)
Hi Rob
Thank you, but it works
but why is the time bit off
it display as 8 hr 29min, shld be 8 hrs 30 mins
9am to 5.30pm
Thank you
Also can advise if I just want to how the duration by days only what is the formula. Thank you
bbsin I haven't yet worked out why it's occasionally 1 minute off.
The simplest formula to get the number of days is =DATEDIF(Start,End,"D").
However if you want to get just working days and omit weekends (Saturday and Sunday) from the calculation then the formula is:
=IF(ISERROR(DATEDIF(Start,End,"d")),"",(DATEDIF(Start,End,"d"))+1-INT(DATEDIF(Start,End,"d")/7)*2-IF((WEEKDAY(End)-WEEKDAY(Start))<0,2,0)-IF(OR(AND(WEEKDAY(End)=7,WEEKDAY(Start)=7),AND(WEEKDAY(End)=1,WEEKDAY(Start)=1)),1,0)-IF(AND(WEEKDAY(Start)=1,(WEEKDAY(End)-WEEKDAY(Start))>0),1,0)-IF(AND(NOT(WEEKDAY(Start)=7),WEEKDAY(End)=7),1,0))
Rob
Los Gallardos
Microsoft Power Automate Community Super User.
Principal Consultant, SharePoint and Power Platform WSP (and classic 1967 Morris Traveller driver)
- Thank you Rob.