Forum Discussion
Generate running time rows/table
- 5 years ago
The algorithm would be something like: (Power Query)
Take the original 'Date Fin' Column and add in a record for 0700 for each date . This will end up as the 'Date Debut' column
-> So take the column , separate date and time, remove the time column. Remove duplicates. Add a column for time 0700. Merge columns to get a table with one column which is 0700 for each date.
Append this table to a table that holds the original 'Date Fin' column. Sort it by datetime.
You'll want to do a similar thing to the original 'Date Debut' column , by adding a 1500 record for each day to make the final 'Date Fin' column.
You then have to find a way of Merging the 2 tables to get the records to line up. I'm thinking of a new column with just the Date in each table. Create an index column with in each day so
1.9.20 0700 1.9.20 1
1.9.20 0805 1.9.20 2
.....
2.9.20 0700 2.9.20 1
You can then Merge the 2 tables on Date and Index columns. After that, you calculate the duration column.
Take each step carefully. Good luck.
The algorithm would be something like: (Power Query)
Take the original 'Date Fin' Column and add in a record for 0700 for each date . This will end up as the 'Date Debut' column
-> So take the column , separate date and time, remove the time column. Remove duplicates. Add a column for time 0700. Merge columns to get a table with one column which is 0700 for each date.
Append this table to a table that holds the original 'Date Fin' column. Sort it by datetime.
You'll want to do a similar thing to the original 'Date Debut' column , by adding a 1500 record for each day to make the final 'Date Fin' column.
You then have to find a way of Merging the 2 tables to get the records to line up. I'm thinking of a new column with just the Date in each table. Create an index column with in each day so
1.9.20 0700 1.9.20 1
1.9.20 0805 1.9.20 2
.....
2.9.20 0700 2.9.20 1
You can then Merge the 2 tables on Date and Index columns. After that, you calculate the duration column.
Take each step carefully. Good luck.
- Anonymous5 years agoNot applicable
Thank you VERY much for your help!!