Forum Discussion
RonaldvdH
Post Patron
Post PatronCalculate 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...
Fowmy
Super User
Super UserRonaldvdH
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.
RonaldvdHPost Patron
Post PatronSo 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