Taking weekend days out of a formula

[Kboz]

I am using the following formula in a sharepoint column to calculate the time that has passed since a file was created:  =DATEDIF(Created,TodaysDate2,"d")&"days "&TEXT(Created-TodaysDate2,"hh")&" hrs"&TEXT(Created-TodaysDate2,"mm")&"min "&TEXT(Created-TodaysDate2,"ss")&"sec "

The TodaysDate2 column is populated using power automate flow that looks at the file each hour and returns the date/time using utcNow().

How do I exclude weekend days from the calculations?

[BCBuizer]

HI @Kboz ,

Perhaps this is what you are looking for: https://powerapps.microsoft.com/de-at/blog/excluding-weekends-and-holidays-in-date-differences-in-po...

[Kboz]

Thank you for the thread, I had already research and read the above link but honestly it makes my head spin.....  How would this be integrated into the formula I currently have?  Also, how will this effect the time - hours, min., sec. - that is currently being calculated.

[BCBuizer]

Hi @Kboz ,

You can just replace your DateDiff() with the mentioned formula and then replace the identifiers for start and end date:

= RoundDown(DateDiff(Created, TodaysDate2, Days) / 7, 0) * 5 +
Mod(5 + Weekday(TodaysDate2) - Weekday(Created), 5) &"days "&TEXT(Created-TodaysDate2,"hh")&" hrs"&TEXT(Created-TodaysDate2,"mm")&"min "&TEXT(Created-TodaysDate2,"ss")&"sec "

[Kboz]

I am replacing the current formula with:

=RoundDown(DateDiff(created.TodaysDate2, Days0 / 7,0) * 5 + Mod(5 = Weekday(created.TodaysDate2) – Weekday(created.TodaysDate2), 5)& “Days "&TEXT(Created-TodaysDate2,"hh")&" hrs"&TEXT(Created-TodaysDate2,"mm")&"min "&TEXT(Created-TodaysDate2,"ss")&"sec "

Sharepoint will not accept the formula.  I am probably just missing something somewhere?

[BCBuizer]

Hi @Kboz , 

There seems to be a bit too much code in what you posted:

This should do the trick:

= RoundDown(DateDiff(Created, TodaysDate2, Days) / 7, 0) * 5 +
Mod(5 + Weekday(TodaysDate2) - Weekday(Created), 5) &"days "&TEXT(Created-TodaysDate2,"hh")&" hrs"&TEXT(Created-TodaysDate2,"mm")&"min "&TEXT(Created-TodaysDate2,"ss")&"sec "

[Kboz]

Well,

changed the formula to:

=RoundDown(DateDif([created], [TodaysDate2], Days) / 7, 0) * 5 +Mod(5 + Weekday([TodaysDate2] – Weekday([Created], 5)&”days “&Text([Created]-[TodaysDate2],"hh")&" hrs"&TEXT([Created]-[TodaysDate2],"mm")&"min "&TEXT([Created]-[TodaysDate2],"ss")&"sec "

Still not acceptable to Sharepoint.... 😞

[BCBuizer]

Dear @Kboz ,

Not sure where all the square brackets are coming from, but can you please just copy the code I'm now providing for the third time:

= RoundDown(DateDiff(Created, TodaysDate2, Days) / 7, 0) * 5 +
Mod(5 + Weekday(TodaysDate2) - Weekday(Created), 5) &"days "&TEXT(Created-TodaysDate2,"hh")&" hrs"&TEXT(Created-TodaysDate2,"mm")&"min "&TEXT(Created-TodaysDate2,"ss")&"sec "

[Kboz]

The square brackets are made when writing the formula in SharePoint.  I just copied the formula exactly as you have it above and Sharepoint gives the following error message:

"The formula contains a syntax error or is not supported."  I have gotten this message with each of the formula variations I have tried.  I apologize for the repetitive nature of my questions and appreciate your patients.

[BCBuizer · Accepted solution]

Hi @Kboz ,

This is a community for PowerApps, hence I thought you were working in PowerApps.

If this is for a SharePoint calculated column, please follow this:

https://social.msdn.microsoft.com/forums/azure/en-US/5978cb6b-6699-4f5d-866f-f13a110602f9/sharepoint...

=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"""))