Hi Mynda,
I have an SAP table that provides me output on
- scheduled start/end day & time : split in 2 columns
- actual start/end day & time : combined in 1 column (sometimes empty depending on the status of the task - we are monitoring during the financial close)
I need to derive the duration and report the status : Started/Finished on time or too late.
The format of the scheduled days & times is fine, but I don't succeed in converting the actuals into the same format to derive the duration
| Description | Status | L start dt | L start tm | L end date | L end time | Actual start time | Actual finish time |
| Task 1 | Completed | 4/2/2020 | 2:59:00 PM | 4/2/2020 | 3:59:00 PM | 20200402080925 | 20200402080926 |
| Task 2 | Not started | 4/7/2020 | 2:59:00 PM | 4/7/2020 | 3:59:00 PM | ||
| Task 3 | Started | 4/6/2020 | 7:59:00 PM | 4/6/2020 | 8:59:00 PM | 20200405210137 |
I worked via "Extract" but I am not sure if this is the correct way to go. I also have difficulties to have my output of duration in a proper layout
Can you give me a hint? Thanks!!
The file is attached
Kr Claudine
Hi Claudine,
You need to split the column into the separate date time parts so you can join it together again using the Date.From function e.g.:
=Date.From(#datetime(year, month, day, hour, minute, seconds))
I hope that points you in the right direction.
Mynda
Hi Mynda
thanks for your help!!
seems I am not seeing the light yet, I tried 2 ways
1. via Extract date & time based on my fixed # of characters (seems to work, but have some issues with the layout of my output (column H of file v2)
2. tried the Date.From as well, but I must do something wrong over there (see file v3)
Kr Claudine
Hi Claudine,
The problem you see in file 2 is that Excel can't display negative dates and or times, there's no such thing, therefore it returns an error. Best to convert it to a decimal time value, which you can display as a negative.
I wouldn't bother with file 3's approach as the file 2 method is easier.
Mynda
