How to split total elapsed time into two rows when it carries over into two dates

[mmunozjr]

Hello,

I am trying to measure the daily downtime of a system to later create metrics on total downtime minutes for the month, average downtime minutes, etc (per system). The challenge that I am having is when I encounter a situation like shown on record ID 3 and 7 when a system downtime duration carries into the next day. What I am trying to accomplish is to separate into two rows the different portions of the corresponding downtime minutes for each separate day for that same system. In essence, all the same column headings should be identical, with perhaps a new column that just identifies the corresponding date, not necessarily the time. This of course would be based on 1440 minutes in a day.

Thank you for your assistance!

[flying008]

Hi, @mmunozjr 

Maybe this is your need ? 

Input
ApplicationName	EventCode	StartDateTime	EndDateTime
HR System	P3	2/1/2023 9:30	2/3/2023 9:10
Finance System	P2	2/5/2023 1:00	2/5/2023 15:20
Sales System	P1	2/15/2023 7:20	2/16/2023 9:25
Shipping System	P2	2/17/2023 5:30	2/17/2023 8:00
HR System	P4	2/19/2023 12:45	2/19/2023 19:00
Sales System	P2	2/22/2023 4:30	2/22/2023 9:00
HR System	P2	2/22/2023 22:00	2/23/2023 2:10
Shipping System	P4	2/25/2023 6:30	2/25/2023 7:00
Output
ApplicationName	EventCode	StartDateTime	EndDateTime	RecordID	RowCount	ElapsedTime(Mins)
HR System	P3	2/1/2023 9:30	2/3/2023 9:10	1	0	870
HR System	P3	2/1/2023 9:30	2/3/2023 9:10	1	1	1440
HR System	P3	2/1/2023 9:30	2/3/2023 9:10	1	2	550
Finance System	P2	2/5/2023 1:00	2/5/2023 15:20	2	0	860
Sales System	P1	2/15/2023 7:20	2/16/2023 9:25	3	0	1000
Sales System	P1	2/15/2023 7:20	2/16/2023 9:25	3	1	565
Shipping System	P2	2/17/2023 5:30	2/17/2023 8:00	4	0	150
HR System	P4	2/19/2023 12:45	2/19/2023 19:00	5	0	375
Sales System	P2	2/22/2023 4:30	2/22/2023 9:00	6	0	270
HR System	P2	2/22/2023 22:00	2/23/2023 2:10	7	0	120
HR System	P2	2/22/2023 22:00	2/23/2023 2:10	7	1	130
Shipping System	P4	2/25/2023 6:30	2/25/2023 7:00	8	0	30

Spoiler

IF [RowCount] = [Row+1:RowCount] 
THEN DateTimeDiff(DateTimeParse([EndDateTime],'%m/%d/%Y %H:%M'), DateTimeParse([StartDateTime],'%m/%d/%Y %H:%M'), 'min') 
ELSEIF [Row+1:RowCount] > [RowCount] && [RowCount] = 0 
THEN 24 * 60 -DateTimeDiff(DateTimeParse([StartDateTime],'%m/%d/%Y %H:%M'), DateTimeParse([StartDateTime],'%m/%d/%Y'), 'min') 
ELSEIF [Row+1:RowCount] > [RowCount] && [RowCount] > 0 
THEN 24 * 60
ELSE DateTimeDiff(DateTimeParse([EndDateTime],'%m/%d/%Y %H:%M'), DateTimeParse([EndDateTime],'%m/%d/%Y'), 'min') 
ENDIF

[mmunozjr]

Hi! Thank you for your prompt and helpful solution. It is almost perfect, just need one minor adjustment. It is possible to adjust the formula to not show or calculate the result shown on line 4 (in red). I may have confused you with my reference to the 1440 minutes in a day. This calculation is not needed. All other rows are perfect.

Finally, would it be possible to add a new column that would identify the date to which that row is referring to?  For example, the first scenario in the HR System P3 870 minutes would be related to 2/1/2023 and the 550 will be related to 2/3/2023 (I made a mistake with the sample date, it should be 2/2/2023. I would greatly appreciate it if you could attach your solution file? Thank you so much!

[flying008]

Hi, @mmunozjr 

If you can upload your want result data as table like me post, then i can adjust it.

the [date] formula:

DateTimeAdd(DateTimeParse([StartDateTime],'%m/%d/%Y'),[RowCount],'day')

******

If can help you get your want, please mark it as s solution and give a like for more share.

[AndrewDMerrill]

Here is a sample workflow, but why do you want to remove the 1440? isn't that part of the down time since a whole 24 hours is included?

[mmunozjr]

Hi Andrew,

Thank you for your reply and helpful solution. The reason the 1,440 line must be deleted is because the total number of minutes for that incident is 1,420. However, it carried over into two days, February 1 and 2. The corresponding split is correct 870 m minutes on the 1st and 550 minutes on the 2nd. the 1,440 minutes was a reference I made to indicate how many minutes in a day in case it was helpful to construct the formula but that number unless an event occurs from midnight to 11:59 PM of the next day should not be a part of the solution. I hope this makes sense. Thanks!

[flying008 · Accepted solution]

Hi, @mmunozjr 

1- Other flow detail by @AndrewDMerrill  for your reference,  because our security policy, I can't upload any flow files.

2- You only need filter the 1440 line after get all time, and the formula of [date]  at the above post. 

3- If you still have questions, please let me know.