Comparing range of Time for a single day

I am trying to compare the range of time

i.e. if time between 8:00:00 am to 11:00:00 am then fill the column as "breakfast" , if time between 12:00:00 am to 16:00:00 am then fill the column as "lunch" else "dinner" using the formula tool.

Approach is by converting the time value to number and then compare the range. The output it gives me in the row is 0,1,-1.

Not sure if there is any other approach I could implement.

Thanks in advance

Workflow

Date Time

Tableau

Data Investigation

Accepted answers

CharlieS

Since you mentioned the numeric conversion process, I'll go ahead and how how that could be done so you have an alternative solution available. Field types and formatting are probably the most tricky thing to get sorted in these cases. If the ideas presented don't work in your application, share an example of the input date/time format and we can help get you on the right track.

I use two formula field to convert the date time value into an numeric hour value, then a second to classify. This could be combined into a single formula if you so desire.

20190718-MealTime.yxmd

All comments

rafalolbert

You've nailed this, there is no other method that would involve less tools, run quicker etc. 🙂

estherb47

Hi @krishma04 ,

Slightly different approach. I like to start with a text table that lists start and end times for each meal

Then, I use the Generate Rows tool to generate all of the times in between, in seconds intervals. It runs SO quickly, even though it's generating thousands of records.

Join your original time field to the TimeMatch field. Whatever maps to a meal will come out of the J, and whatever doesn't will come out of the L.

What matchedWhat didn't match

Let me know if this works for you.

Cheers!

Esther

CharlieS

I use two formula field to convert the date time value into an numeric hour value, then a second to classify. This could be combined into a single formula if you so desire.

20190718-MealTime.yxmd

krishma04

Thank you Esther, I'll try it out and see if it would work!

Krishma

This email and any attachments are confidential and may be privileged or otherwise protected from disclosure. If you are not the intended recipient, please telephone or email the sender and delete this message and any attachment from your system immediately. If you are not the intended recipient you must not copy this message or attachment or disclose the contents to any other person.

krishma04

Thanks Charlie, your solution worked perfectly!

Got the desired output 😄

Cheers!

Krishma

DauteriveA

So when I use your method to fill in all times of the day with the specific time period, I have one issue. When working at 20:00 (8PM) to Midnight (11:59:59). I run into an issue with it loop continuously.

RichardChu

@CharlieS Good solution!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703