Forum Discussion
Calculate time between
Hey guys, I have a problem and i'm hoping for your help. I use this formula to calculate the hours between 2 dates and it works like a charm, the variable is that it calculates the hours between...
RonaldvdH
I asked for a dummy file to streamline our communication and expedite the solution process, minimizing back-and-forth exchanges. This enables me to clearly understand the model you're working on, facilitating a more direct solution.
In any case, the recommended approach is to generate a table with seven rows, each corresponding to a day (1, 2, 3, and so forth), along with start and end times. By iterating through this table, you can determine the time range necessary for your calculations.
So my ServiceWindow table should be adjusted to this ?
| Day | Begin | End |
| Monday | 08:00:00 | 20:00:00 |
| Tuesday | 08:00:00 | 20:00:00 |
| Wednesday | 08:00:00 | 20:00:00 |
| Thursday | 08:00:00 | 20:00:00 |
| Friday | 08:00:00 | 20:00:00 |
| Saturday | 08:00:00 | 12:00:00 |
But what would the formula be then ?
Doorlooptijd Ticket in Uren =
VAR ServiceWindowStart = Calculate(SELECTEDVALUE(ServiceWindow[ServiceWindow]),ServiceWindow[Dag]="Start")
VAR ServiceWindowEind = Calculate(SELECTEDVALUE(ServiceWindow[ServiceWindow]),ServiceWindow[Dag]="Eind")
VAR ServiceWindowPerDag = VALUE(ServiceWindowEind-ServiceWindowStart)*24
VAR DezeStartTijd = Incidenten[StartTijd]
VAR DezeEindTijd = Incidenten[EindTijd]
VAR StartDatum =Incidenten[StartDate]
VAR EindDatum = Incidenten[EndDate]
VAR EersteDagVerstrekenTijd = Switch(TRUE(),
Related('Date'[ServiceWindow])=0,0,
DezeStartTijd>=ServiceWindowEind,0,
StartDatum = EindDatum && DezeEindTijd < ServiceWindowEind,Round((DezeEindTijd-DezeStartTijd)*24,3),
ROUND((ServiceWindowEind-DezeStartTijd)*24,3))
VAR LaatsteDagVerstrekenTijd = Switch(TRUE(),
LOOKUPVALUE('Date'[ServiceWindow],'Date'[Date],EindDatum)=0,0,
DezeEindTijd<=ServiceWindowStart,0,
DezeEindTijd>=ServiceWindowEind,ServiceWindowPerDag,
StartDatum = EindDatum ,0,
ROUND((DezeEindTijd-ServiceWindowStart)*24,3))
VAR VolledigeWerkdagen = Calculate (sum('Date'[ServiceWindow]),DATESBETWEEN('Date'[Date],StartDatum+1,EindDatum-1))
VAR TotaalAantalUren = EersteDagVerstrekenTijd+VolledigeWerkdagen*ServiceWindowPerDag+LaatsteDagVerstrekenTijd
Return TotaalAantalUren
Fowmy first of all best wishes for 2024 😉
I was still hoping you could help me out with my problem, any ideas ?