cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Krutigawale33
Responsive Resident
Responsive Resident

Calculated Column

Hello All,

 

I have requirement where I need to calculate no of days to complete for Change request to meet the SLA.

I have below table where "Time to Complete in Hours and Minutes","Number of days to complete","Created On (Day of the Week)", "Completed on( Day of week)" this are calculated columns of my table.

 

where this columns are calculated like below

 Time to Complete in Hours and Minutes=Released At Time Stamp-Created At Time Stamp

 

Number of days to complete=24*((Time to Complete in Hours and Minutes*60)/1440)

 

I have to calculate the no of days by considering non-busniness days.

For example the change request 1085, created day of week is friday and completed day of week is tuesday.

so its contains saturday and sunday, we need to avoid this and need to consider only business days to show  No of days to complete. Right now the result is showing 4 days. but actual result should be 3 days.

 

How to do that is there any way? to skip the non-business days.

 

 

Change RequestTypeCreated At Time StampReleased At Time StampTime to Complete in Hours and MinutesNumber of days to completeCreated On (Day of the Week)Completed on( Day of week)
100xyz1/20/20 17:461/22/20 19:4349:57:462.08MondayWednesday
1004xyz1/30/20 17:462/4/20 15:29117:42:394.90ThursdayTuesday
1007xyz1/30/20 18:162/6/20 13:05162:48:506.78ThursdayThursday
1017xyz1/30/20 19:051/31/20 17:2622:20:330.93ThursdayFriday
102xyz1/20/20 17:501/28/20 12:48186:57:237.79MondayTuesday
1030xyz1/30/20 19:452/10/20 17:21261:36:1410.90ThursdayMonday
1037xyz1/30/20 20:112/4/20 20:48120:37:265.03ThursdayTuesday
105xyz1/20/20 17:581/22/20 17:0847:10:131.97MondayWednesday
1078xyz1/31/20 10:422/4/20 14:45100:03:034.17FridayTuesday
1082xyz1/31/20 13:292/4/20 14:4597:16:264.05FridayTuesday
1085xyz1/31/20 13:452/4/20 14:4596:59:544.04FridayTuesday
1087xyz1/31/20 13:562/4/20 14:4496:48:534.03FridayTuesday
1090xyz1/31/20 14:151/31/20 17:273:11:410.13FridayFriday
1130xyz1/31/20 21:312/4/20 15:2989:57:263.75FridayTuesday
1137xyz1/31/20 22:322/4/20 15:2988:56:143.71FridayTuesday
115xyz1/20/20 18:361/28/20 12:48186:12:177.76MondayTuesday
124xyz1/20/20 19:081/22/20 18:2547:16:351.97MondayWednesday
146xyz1/20/20 20:121/23/20 16:1268:00:052.83MondayThursday
173xyz1/21/20 13:491/23/20 17:2651:36:372.15TuesdayThursday
174xyz1/21/20 14:021/29/20 20:17198:15:188.26TuesdayWednesday
179xyz1/21/20 14:341/28/20 12:47166:12:536.93TuesdayTuesday
223xyz1/21/20 18:211/23/20 16:0245:41:081.90TuesdayThursday
263xyz1/21/20 20:291/23/20 18:0045:31:321.90TuesdayThursday
264xyz1/21/20 20:321/22/20 17:4121:09:430.88TuesdayWednesday
265xyz1/21/20 20:391/23/20 11:3538:56:221.62TuesdayThursday
266xyz1/21/20 20:421/29/20 14:46186:03:547.75TuesdayWednesday
278xyz1/21/20 21:571/22/20 19:1821:20:200.89TuesdayWednesday
284xyz1/21/20 22:291/27/20 16:55138:25:345.77TuesdayMonday
292xyz1/22/20 13:472/10/20 21:02463:14:0619.30WednesdayMonday
312xyz1/22/20 15:341/23/20 16:1324:38:511.03WednesdayThursday
315xyz1/22/20 16:121/27/20 20:15124:02:395.17WednesdayMonday
337xyz1/22/20 17:421/30/20 9:43184:00:557.67WednesdayThursday
359xyz1/22/20 19:131/28/20 22:17147:03:486.13WednesdayTuesday
392xyz1/22/20 21:101/27/20 13:13112:02:394.67WednesdayMonday
402xyz1/22/20 21:471/23/20 16:2218:34:550.77WednesdayThursday
403xyz1/22/20 21:471/28/20 19:07141:19:525.89WednesdayTuesday
430xyz1/23/20 11:411/23/20 16:064:24:350.18ThursdayThursday
432xyz1/23/20 12:001/23/20 16:054:04:210.17ThursdayThursday
442xyz1/23/20 14:021/24/20 13:2123:18:520.97ThursdayFriday
453xyz1/23/20 16:121/27/20 22:12101:59:374.25ThursdayMonday
469xyz1/23/20 19:011/23/20 19:590:57:580.04ThursdayThursday
471xyz1/23/20 19:181/23/20 20:010:43:100.03ThursdayThursday
519xyz1/23/20 22:001/28/20 13:06111:05:104.63ThursdayTuesday
530xyz1/24/20 2:271/24/20 19:0416:36:570.69ThursdayFriday
538xyz1/24/20 3:311/24/20 15:4912:17:510.51ThursdayFriday
631xyz1/24/20 19:521/30/20 19:52143:59:426.00FridayThursday
646xyz1/24/20 21:331/27/20 13:3764:03:592.67FridayMonday
694xyz1/27/20 17:371/28/20 13:1219:35:140.82MondayTuesday
73xyz1/20/20 16:021/23/20 16:0472:01:513.00MondayThursday
742xyz1/27/20 20:191/30/20 9:4061:21:462.56MondayThursday
746xyz1/27/20 20:532/7/20 16:38259:44:3210.82MondayFriday
788xyz1/28/20 17:011/31/20 21:1176:09:273.17TuesdayFriday
887xyz1/29/20 16:172/7/20 16:38216:20:449.01WednesdayFriday
978xyz1/30/20 13:181/30/20 19:556:36:320.28ThursdayThursday
995xyz1/30/20 16:271/30/20 19:343:07:440.13ThursdayThursday
1 ACCEPTED SOLUTION
3 REPLIES 3

Hello @amitchandak ,

I am able to achieve that. But does it considers the timestamp? 

 

Means Created Time will have time 9/21/2021 1:57:33 PM and Released Time 9/22/2021 11:53:03 AM with this if gets difference then will get 9:55:30 , completed in 1 day but with above approach it considers 2 workdays

 

Any idea how to do that

Hello Amit,

 

I am getting this error while creating DateDiff column. How to deal with this

Krutigawale33_1-1618930717498.png

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

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

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

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors