Forum Discussion

Phishdawg's avatar
Phishdawg
Brass Contributor
Apr 02, 2023

Need Formula that Accounts for Blank Date

I'm still strruggling to resolve the following issue. TicketDate (B) - formatted as Date InTime (C) - formatted as Date OutTime (D) - formatted as Date WaitTime (E) - formatted as Time ...
Formulas and Functions
HansVogelaar's avatar
HansVogelaar
MVP
Apr 02, 2023

Phishdawg 

It is not possible to have a formula in column D that returns a result if column D itself is blank - that results in a circular reference. Moreover, when you enter a date in column D, you'd overwrite the formula.

For WaitTime, you can use the formula

 

=IF([@OutTime]="",TODAY(),[@OutTime])-[@InTime]

 

Format the WaitTime column as General or as Number with 0 decimal places.

  • Phishdawg's avatar
    Phishdawg
    Brass Contributor
    Apr 02, 2023

    HansVogelaar 
    I like the output created by the formula below. Using the your new suggestion produces in accurate 'WaitTime' and adds unwanted additional information. I may have to deal with the blanks on the Power Automate side.

     

     

    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Apr 02, 2023

      Phishdawg 

      There was no way I could have known that the InTime and OutTime columns include the time, since they are formatted to display only the date.

      You might use

       

      =IF([@OutTime]="", "", [@OutTime]-[@InTime])

Resources