Calculations using time fields

Hi all,

I have two time ranges (e.g. range 1: 08:00:00-12:30:00 range 2: 18:00:00-24:00:00), and am trying to work out an Easymorph formula to determine whether range 1 completely, partially, or doesn’t cover range 2. I’m not sure of the syntax I need to use, as the < > symbols result in an ‘#Incomparable values for ‘>’’ error.

Does anyone know the answer?

Thanks,
Alistair

Hi Alistair,

you can use the comparison operators (>, <, etc.) as long as the compared values are numbers. Dates and timestamps in EasyMorph are also numbers, similarly to Excel. You may need to convert text dates and timestamps to numbers using date() function in order to use the comparison operators. See the example below.

time ranges.morph (4.5 KB)

Hi Dmitry,

Thanks for your quick reply!

I formatted the start and end times like this, which appeared to get the timestamp in the format that I wanted, but I’m not sure it was being read it as a number. Do I need to include a date field for it to work?

Not sure I understand your question. In your screenshot you’re using not date() but format() function which performs the opposite to date() operation – converts numbers to text. To convert text to numbers you should use date(). Otherwise you won’t be able to use the comparison operators.

You can see if a value is a number or text by alignment in cell. If a value is aligned left – it’s a text. If right – it’s a number.

Hi Dmitry,

I can’t seem to get it to work. Here’s the Range B times imported into Easymorph

Which looks like this when imported

I’ve modified Start and End times using different formulas here to test them

Which produces

I must be missing something, any chance you know what it is?

This seems to me already perfect for comparison. I believe you don't need to convert [Opening time] and [Closing time] at all because they are already numbers when imported. If the range you're comparing to is in [Session name] then all what you need is to parse [Session name]. Let me know you if need help with this.

Yes it’s worked, I’ve removed the conversion calcs altogether and it works.

1 Like