cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LiamR89
Helper I
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
LiamR89
Helper I
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.

yashag2255
Dual Super User II
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!

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Demo Extravaganza is Back!

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

MBAS on Demand

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)