Forum Discussion

username1231231885's avatar
username1231231885
Copper Contributor
Aug 17, 2023

Column Calculation: Dates

Hi, 

 

So I am using this for the calculation: 

=IF(ISBLANK([Completed Date]),0,IF([Completed Date]>=[Start Date],DATEDIF([Start Date],[Completed Date],"d"),"Invalid Date"))

 

I would like to have fractional days such as the sample is started in the morning but completed in the afternoon, getting number like 0.5 

 

But for those cases, I am keep getting 0.0 

 

Any input will be appreciated, thank you. 

Lists
Microsoft Lists
SharePoint Online
  • Rob_Elliott's avatar
    Rob_Elliott
    Bronze Contributor
    Aug 30, 2023

    username1231231885 make sure your date/time columns are set to time as well as date then use  something similar to the following formula in your calculated column to give you the fraction of a full day (24 hours). The calculated column has a number output with 1 decimal place.

    =INT([End Date]-[Start Date])+HOUR([End Date]-[Start Date])/24

     

     

    Rob
    Los Gallardos
    Microsoft Power Automate Community Super User.
    Principal Consultant, SharePoint and Power Platform (and classic 1967 Morris Traveller driver)

     

Resources