Formulae: Bucketing time values?

DanVatcu · December 14, 2012 3:30PM

I have a time field that was imported as text in Omniscope (let's call it TIME). The field contains only time without date and the format is HH:mm:ss.

Based on the TIME field , I need a formula to convert it into a new text field (let's call it TIME_OF_THE_DAY) as follows:

If TIME is between 00:00:00 and 06:59:59 then TIME_OF_THE_DAY field value should be 'night'
If TIME is between 07:00:00 and 11:59:59 then TIME_OF_THE_DAY field value should be 'morning'
If TIME is between 12:00:00 and 17:59:59 then TIME_OF_THE_DAY field value should be 'afternoon'
If TIME is between 18:00:00 and 23:59:59 then TIME_OF_THE_DAY field value should be 'evening'

How can I do it? Can you suggest a script?
Thanks

tjbate · December 17, 2012 9:13AM

Dan - A proper Date/Time field is a fixed point in time (stored internally in the computer as the number of milliseconds since January 1st, 1970). In order to be correctly typed Date/Time in Omniscope, a single field must be fully-specified with day, month, year, time of day and ideally time zone as well.

Spreadsheets frequently store date and time in separate columns/fields, but this is very bad practise. So, first step is to concatenate the date and the time being imported from the spreadsheet (with a space between) as Text, then convert the full combined Text string to the correct proper Date/Time field in Omniscope using something like:

TEXTTODATE(([Date]&" "&[Time]),"dd/MM/yyyy HH:mm:ss"))

If the date is always today's date, you can use the TODAY function to supply the date.

tjbate · December 17, 2012 10:53AM

Once you have a proper Date/Time field, you can use a nested IF statement like this:

IF(DATEUNIT([Date/Time],"hours") <6,"night",

IF(DATEUNIT([Date/Time],"hours") <12,"morning",

IF(DATEUNIT([Date/Time],"hours") <18,"afternoon",

"night")))

I have posted an example file

DanVatcu · December 17, 2012 1:33PM

It worked. Great support :) Thanks!

Welcome!

Categories

Ideas Parade

Tagged