cancel
Showing results for
Did you mean:
Helper I

## DateDiff & some maths

Hi all,

Trying to track how long certain processes take within an app.

I can use datediff(created,checked,minutes) and get an answer. I'd like it to be more accurate and count only working minutes (office hours) though.

Any ideas on this, I had an idea, but doesn't seem to be working correctly how I expected (bad maths probably)

Sum(DateDiff(ThisItem.Created,ThisItem.CheckedTime,Minutes)-Sum(Day(ThisItem.CheckedTime)-Day(ThisItem.Created))*14*60)
So my total mins, - number of days between dates x 14 (assuming 10 hour work day) * 60 to convert to mins only problem is i appear to be 20 hours out.

2 REPLIES 2
Helper I

Okay, so my 20 hours out is due to me counting 10 working days for each day of the weekend, rather than excluding these hours completely, so i need a way of removing these.

Dual Super User II

Hi @LiamR89

To exclude weekends from the count, you can update the configuration to:

Set(_inclusiveTotalDaysRequested, DateDiff(ThisItem.Created,ThisItem.CheckedTime, Days) + 1);
Set(_numFullWeeks, RoundDown(_inclusiveTotalDaysRequested / 7, 0));
Set(_numFullDaysPartialWeek, _inclusiveTotalDaysRequested - _numFullWeeks * 7);
Concurrent(Set(_startWeekday, Weekday(LeaveStartDatePicker.SelectedDate)), Set(_endWeekday, Weekday(LeaveEndDatePicker.SelectedDate)));
If(_numFullDaysPartialWeek = 6,
If(_startWeekday <= 2, Set(_numPartialWeekdays, 5), Set(_numPartialWeekdays, 4)
),
_numFullDaysPartialWeek = 5,
If(_startWeekday = 2, Set(_numPartialWeekdays, 5), _startWeekday = 1 || _startWeekday = 3 || _startWeekday = 4, Set(_numPartialWeekdays, 4), Set(_numPartialWeekdays, 3)
),
_numFullDaysPartialWeek = 4,
If(_startWeekday = 2 || _startWeekday = 3, Set(_numPartialWeekdays, 4), _startWeekday = 1 || _startWeekday = 4, Set(_numPartialWeekdays, 3), Set(_numPartialWeekdays, 2)
),
_numFullDaysPartialWeek = 3,
If(_startWeekday = 6 || _startWeekday = 7, Set(_numPartialWeekdays, 1), _startWeekday = 1 || _startWeekday = 5, Set(_numPartialWeekdays, 2), Set(_numPartialWeekdays, 3)
),
_numFullDaysPartialWeek = 2,
If(_startWeekday = 7, Set(_numPartialWeekdays, 0), _startWeekday = 1 || _startWeekday = 6, Set(_numPartialWeekdays, 1), Set(_numPartialWeekdays, 2)),
_numFullDaysPartialWeek = 1,
If(_startWeekday = 1 || _startWeekday = 7, Set(_numPartialWeekdays, 0), Set(_numPartialWeekdays, 1)
),
_numFullDaysPartialWeek = 0, Set(_numPartialWeekdays, 0)
);
Set(_workDaysInRequest, _numFullWeeks * 5 + _numPartialWeekdays);

Set(finalOutput, _workDaysInRequest * 14 * 60)

Here, you can use the finalOutput variable as the output.

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

Announcements

#### Welcome to the User Group Public Preview

Check out how to claim yours today!

#### Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

#### Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Top Solution Authors
Top Kudoed Authors
Users online (70,573)