Forum Discussion

spandy34's avatar
spandy34
Responsive Resident
3 years ago
Solved

Calculating time Midnight

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 P...
Need Help
  • spandy34's avatar
    spandy34
    3 years ago

    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's avatar
    spandy34
    Responsive Resident
    3 years ago

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

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      3 years ago

      spandy34 OK, try this:

      Planned Duration = 
  IF(
    [PlannedExitTime] = TIME(0,0,0),
    (TIME(23:59:59) - [PlannedEntryTime) + TIME(0,0,1),
    [PlannedExitTime] - [PlannedEntryTime]
  )
      • spandy34's avatar
        spandy34
        Responsive Resident
        3 years ago

        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]
          )