Calculating time Midnight

[spandy34]

I have the table below.  I have a Plannned Entry Time and a Planned Exist Time and a Planned Duration which are all Time fields.  I have used the following formula for the Planned Duration Column

Planned Duration = [PlannedExitTime]-[PlannedEntryTime].  It works okay until like the example the Planned Entry TIme is 23:45:00 and then the Planned Exit Time is Midnight.  The Planned Duration states 23:45:00 instead of 00:15:00

Can someone help with the formula in the Planned Duration Column to resolve this please.

@amitchandak @Greg_Deckler 

[Greg_Deckler]

@spandy34 Is this being done in Power Query?

[spandy34]

No not power query . I just added the column in the table imported from an sql database 

[Greg_Deckler]

@spandy34 OK, try this:

Planned Duration = 
  IF(
    [PlannedExitTime] = TIME(0,0,0),
    (TIME(23:59:59) - [PlannedEntryTime) + TIME(0,0,1),
    [PlannedExitTime] - [PlannedEntryTime]
  )

[spandy34 · Accepted solution]

THis has solved it - thank you so much for your help

Planned Duration =

  IF(

    [PlannedExitTime] = TIME(0,0,0),

   (TIME(23,59,59)- 'Procurement_Main_Data'[PlannedEntryTime])+TIME(0,0,1),

   [PlannedExitTime] - [PlannedEntryTime]

  )

[spandy34]

Hi @Greg_Deckler 

Ive tried adding a column with the above Dax and I am getting the error below

[AndrewPF]

Are you taking into account the fact that anything after midnight is the following day? 

Do your time fields include a date? 

See also: 

Solved: Concatenate date and time from different columns i... - Microsoft Power BI Community

[spandy34]

no it doesn't include a date. I think that was the problem . I think I need some DAX thst says if the time equals midnight then it's handled differently than just [PlannedExitTime]-[PlannedEntryTime]