Problem Getting formula for date and time calculation to work properly

Copper Contributor

I have the formula below in a Sharepoint column to calculate the amount of time that has passed since a file was created.  The formula is not returning the proper number of days - calculating one more day than it should.

 

=IF(OR(ISBLANK(Created),ISBLANK(TodaysDate2)),"",IF(AND((WEEKDAY(TodaysDate2,2))<(WEEKDAY(Created,2)),((WEEKDAY(Created,2))-(WEEKDAY(TodaysDate2,2)))>1),(((DATEDIF(Created,TodaysDate2,"D")))-(FLOOR((DATEDIF(Created,TodaysDate2,"D"))/7,1)*2)-2),(((DATEDIF(Created,TodaysDate2,"D")))-(FLOOR((DATEDIF(Created,TodaysDate2,"D"))/7,1)*2)))&" days, "&TEXT(MOD(TodaysDate2-Created,1),"hh "" hrs, "" mm "" mins"""))

2 Replies

@Kaptain_1959  I thought I had it once I set the date/time to show hours... and fixed my time zone so it was the same in the site as my profile...  but nope, I'm exactly a day off.  I'll play around with it some more. 

 

Thanks, I have tried to also getting todays date and time using a power automate flow and changing the date from UTC time to Central Standard time, (convertFromUtc(utcNow(), 'Central Standard Time'). This seems to fix the day issue but hours are still incorrect. This flow fills in the TodaysDate Column in Sharepoint.