Date Differences

(0) Share

Report

Posted on by vffdd

1,257 Super User 2024 Season 1

I'm aware there are lots of posts on this topic, just can't seem to find the right one to suit my query ! 🙂

I'm looking to find the number of business/week days between two dates. One date is in a datepicker field and the other one is a text field which is being dynamically converted to a date field in the forumula. It's working fine for me to get the number of days using:

"Days Open: " & DateDiff(DateValue(DCVCreated.Text),dateToday.SelectedDate,Days)

DCVCreated is a text field and dateToday is a date field

That's fine returns the actual number of days between those two dates, However I only want the week days counted and have been trying to figure that out using the info here

https://powerapps.microsoft.com/es-es/blog/excluding-weekends-and-holidays-in-date-differences-in-powerapps/

but I just can't seem to get the syntex right..

Any ideas?

many thanks

Categories:

Building Power Apps

All responses (3)

Answers (0)

Sort by

mdevaney 29,993 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

@vffdd

Please give an actual example of dates that you tried and share what the result from the calculation was. Share your full code. It would also be helpful to see a screenshot of the app.

In general, I have found the formula you referenced to be successful. So that's why I am asking for more information.

https://powerapps.microsoft.com/es-es/blog/excluding-weekends-and-holidays-in-date-differences-in-po...

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."
vffdd 1,257 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
So the formula as per the linked article works as far as below 1., so that gives me an answer but it's not ideal because won't work for any dates that are less than a week ago.

1.
"Week Days Open: " & RoundDown(DateDiff(DateValue(DCVCreated.Text),dateToday.SelectedDate,Days) / 7, 0) *5

However If I try to do the full forumula
"Week Days Open: " & RoundDown(DateDiff(DateValue(DCVCreated.Text),dateToday.SelectedDate,Days) / 7, 0) *5+ Mod(5 + Weekday(dateToda.SelectedDate) - Weekday(DateValue(DCVCreated.Text), 5)

It says the Weekday function as invalid arguments. I know it must be just something simple I'm missing or have done wrong in the syntax? but I have copied it exactly as per the article and as I said it works if I remove the last part of it..
mdevaney 29,993 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

@vffdd

Can you supply a screenshot of which WEEKDAY function is being highlighted red in the formula bar (there are 2 in your formula).

Also, can you show a screenshot of a value entered into DCVCreated?

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."