Forum Discussion
time calculation
Dear all,
I have a question about the following.
Below is a small table with date-times.
I want to show the date_time which is the closest to 07:15.
Both the closest before 07:15 and the closest after 07:15.
I have a calendar table, so I want to do this for every date in the table.
In the second table, the outcome is shown.
| time_executed |
| 29-6-2021 07:00 |
| 29-6-2021 07:41 |
| 29-6-2021 12:31 |
| 29-6-2021 13:38 |
| 29-6-2021 13:54 |
| 29-6-2021 14:17 |
| 29-6-2021 15:53 |
| 29-6-2021 19:06 |
| 29-6-2021 19:24 |
| 29-6-2021 20:28 |
| 29-6-2021 22:20 |
| 29-6-2021 22:21 |
| 30-6-2021 04:42 |
| 30-6-2021 04:49 |
| 30-6-2021 04:50 |
| 30-6-2021 04:59 |
| 30-6-2021 05:00 |
| 30-6-2021 05:02 |
| 30-6-2021 05:20 |
| 30-6-2021 05:30 |
| 30-6-2021 06:09 |
| 30-6-2021 06:18 |
| 30-6-2021 06:18 |
| 30-6-2021 06:28 |
| 30-6-2021 07:35 |
| date | time_before_07:15 | time_after_07:15 |
| 29-6-2021 | 29-6-2021 07:00 | 29-6-2021 07:41 |
| 30-6-2021 | 30-6-2021 06:28 | 30-6-2021 07:35 |
Try this:
Before 7:15 = CALCULATE ( MAX ( [time_executed] ), FILTER ( YourTable[time_executed], YourTable[time_executed] < 'Calendar'[Date] + 0.302083333333333 ) )After 7:15 = CALCULATE ( MIN ( [time_executed] ), FILTER ( YourTable[time_executed], YourTable[time_executed] > 'Calendar'[Date] + 0.302083333333333 ) )P.S. 0.302083333333333 is the numerical value of 7:15 AM
4 Replies
Try this:
Before 7:15 = CALCULATE ( MAX ( [time_executed] ), FILTER ( YourTable[time_executed], YourTable[time_executed] < 'Calendar'[Date] + 0.302083333333333 ) )After 7:15 = CALCULATE ( MIN ( [time_executed] ), FILTER ( YourTable[time_executed], YourTable[time_executed] > 'Calendar'[Date] + 0.302083333333333 ) )P.S. 0.302083333333333 is the numerical value of 7:15 AM
Is there a way to calculate the numeric value of a certain time?
Yes... The easiest way is to enter it in Excel cell then change the cell format to decimal 🙂