If Isnull or 00:00:00

[Ciara]

I have a table that has a number of different timestamps  Time1DateTime, Time2DateTime, Time3DateTime etc and they are in the format DD/MM/YYYY hh:mm:ss

I'm looking to find the interval (in minutes) between Time6DateTime and Time7DateTime which is easy enough 😁 but we ALL know users who enter data never make things easy!! 

So if Time7 is null then use time Time8 and if thats null then use Time9 etc.

Currently I have the below calculation:

Interval(If(IsNull(Time7DateTime),(If(IsNull(Time8DateTime),Time9DateTime,Time8DateTime)),Time7DateTime) - (If(IsNull(Time6DateTime),(If(IsNull(Time5DateTime),Time4DateTime,Time5DateTime)),Time6DateTime)), 'mm') as LengthofSurgery

This works beautifully....................until (there is always an until) users enter 00:00:00 for the time. 🙄

So now I would like my calculation to be:  if Time7DateTime is null OR if the time portion of Time7DateTime is 00:00:00 then use Time8DateTime etc etc.

HELP! 😭

[sunny_talwar]

May be this

Interval(
If(Alt(Time7DateTime, 0) = 0, (If(Alt(Time8DateTime, 0) = 0, Time9DateTime, Time8DateTime)), Time7DateTime) - (If(Alt(Time6DateTime, 0) = 0,(If(Alt(Time5DateTime, 0) = 0, Time4DateTime, Time5DateTime)), Time6DateTime)), 'mm') as LengthofSurgery

[Ciara · Accepted solution]

Hi Sunny

Thanks for the reply.  I actually worked on this last night and came up with the following:

Interval((If(Time#(Time7) > (Time#('00:00:00')), Time7, If(Time#(Time8) > (Time#('00:00:00')), Time8, Time9)) - If(Time#(Time6) > (Time#('00:00:00')), Time6, If(Time#(Time5) > (Time#('00:00:00')), Time5, Time4))),'mm') as LengthofSurgery

Seems to work well for the most part (I still have issues where there are missing time but can't do anything about that 😊

Thanks for your time though.

Ciara