Forum Discussion

bbsin's avatar
bbsin
Iron Contributor
Dec 11, 2023
Solved

Calculate days and hr min based on start and end date in SP

hi
I like to auto calculate the duration

1) to only display hr and min

2) to only display total days 

3) also to have space for hr and min 

 

May I know how to?

=CONCATENATE(INT(FacilityBookedEnd-EventStartDateTime),"Days",TEXT(EventEndDateTime-EventStartDateTime,"h""hrs""m""mins"""))

Thank you

 

SharePoint Online
  • Rob_Elliott's avatar
    Rob_Elliott
    Dec 11, 2023

    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)

4 Replies

  • Rob_Elliott's avatar
    Rob_Elliott
    Bronze Contributor
    Dec 11, 2023

    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)

    • bbsin's avatar
      bbsin
      Iron Contributor
      Dec 12, 2023

      Rob_Elliott 

      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

       



      • Rob_Elliott's avatar
        Rob_Elliott
        Bronze Contributor
        Dec 12, 2023

        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)

Resources