How to Pull the Day of the Week and time of the day from Date/time Field?

[Anonymous]

Hi Everyone,

I am looking to build a dashboard where I can show Email's Open day of week and time of day. I have a Date/Time Field called "Activity Date".

Dimension:

Activity Type: Email Opens, Website Visits, Form Submitted etc..

I need to show the data in a timeline (time of day and day of week). For day of week I used the weekday([Activity Date]) function to pull out the week information.

Something like this:

Any suggestions on showing the data in time of day?

[tresesco]

Have you tried time()?

[Anonymous]

I used Time function it gives me the below output

What I am looking for is something like 6:14 AM, 8:54 AM, 5:42 AM or 6 AM, 8 AM, 5 AM, 1 PM. Basically analyze the Activity Type(Email Opens, Website Visits etc) by time of the day.

[tresesco]

Use format code with time() like:

Time(YourTimeField, 'hh:mm TT')

[Anonymous]

Thank you Tresesco. It worked. Also how do I show a trend line for eg: starting from morning 08:00 AM to 06:00PM.

Also how do I group the time?

eg: I would like to group all the below records for 1:05 AM or 01:02 AM or buckets of time 01:00 AM - 02:00 AM, 02:00 AM - 03:00 AM. etc and also how can I sort this data?