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 2 dates based on a service window (monday till saturday 08.00 till 20.00)
The table servicewindow is a basic table with:
| Day | ServiceWindow |
| Start | 08:00:00 |
| Eind | 20:00:00 |
The problem is that the servicewindow for saterday has changed to 08.00 till 12.00
Maybe I need an extra VAR for a ServiceWindowWeekend ? or a formula that checks the StartDate (or EndDate) and if it's a Saturday it used different Start/Eind times
There is a relationship between this Incidents table and the Date table based on StartDate but I need an additional condition in Var EersteDagVerstrekenTijd and VAR LaatsteDagVerstrekenTijd.
Both VAR's calculate based on a 12 hour servicewindow (monday through saturday) but:
Examples for what i'm expecting:
- if a ticket starts on a Saturday > 12.00 the VAR EersteDagVerstrekenTijd should be 0
- if a ticket ends on a Saturday > 12.00 the VAR LaatsteDagVerstrekenTijd should be 4
- If a ticket starts on Friday 16.00 and ends on Monday on 12.00 the formula now calculates that there are 20 hours between both dates (4 hours friday, 12 hours saturday and 4 hours monday) but the hours on saturday should be 4 (ServiceWindow Saturday: 08.00-12.00) so a total of 12 hrs
- If a ticket starts on Thursday 19.00 and ends on Saturday on 14.00 the formula now calculates that there are 19 hours between both dates (1 hour Thursday, 12 hours Friday and 6 hours Saturday) but the hours on saturday should be 4 (ServiceWindow Saturday: 08.00-12.00) so a total of 17 hrs
- If a ticket starts on Saturday 16.00 and ends on Monday on 09.00 the formula now calculates that there are 5 hours between both dates (4 hours Saturday and 1 hour on Monday) but the hours on saturday should be 0 (ServiceWindow Saturday: 08.00-12.00) so a total of 1 hrs
Can anyone help me out here ?
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
6 Replies
Any ideas besides me sharing the file ?
I was hoping that a adjustment to the formula to take in account the saturday was the way to go
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
