topic How to create Morning and Afternoon fields in data load based on time of day? in App Development

How to create Morning and Afternoon fields in data load based on time of day?

mikegrattan — Sat, 16 Nov 2024 18:22:40 GMT

My Time field is formatted as follows:

time(Date#(textBetween(observation_time,'Last Updated on ', 'P'),'MMM DD YYYY, hh:mm tt')) as Time

I would like to create another field that can have the values of Morning or Afternoon, depending on the Time field. If the time is between 04:00:00 AM and 11:59:59 AM, then it is morning. If it is between 12:00:00 PM and 5:00:00 PM, then it is evening.

Help is appreciated very much. Thank you.

Re: How to create Morning and Afternoon fields in data load based on time of day?

Anonymous — Mon, 13 Jul 2020 19:38:47 GMT

if(Wildmatch(YourTimeField,'*AM*'),'Morning','Afternoon') as "Morning | Afternoon"

(example with Now())

Re: How to create Morning and Afternoon fields in data load based on time of day?

mikegrattan — Mon, 13 Jul 2020 21:12:02 GMT

Thank you for your reply. That looks like it would be great for generic AM/PM filtering but we wanted specific time frames for Morning vs Afternoon. Is there another approach that would provide that functionality?

Re: How to create Morning and Afternoon fields in data load based on time of day?

Anonymous — Tue, 21 Jul 2020 17:32:46 GMT

You can build this as a master Item or in the data load:

= if( Time(Frac(observation_time))> '4:00:00 AM' and Time(Frac(observation_time))<'11:59:59 AM', 'Morning'
,if( Time(Frac(observation_time))> '12:00:00 PM' and Time(Frac(observation_time))< '5:00:00 PM', 'Evening','N/A'))

Re: How to create Morning and Afternoon fields in data load based on time of day?

mikegrattan — Tue, 21 Jul 2020 18:43:59 GMT

That works; I was not aware of the Frac function so I learned something new today. Thanks!