Let's add some context: So, I was asked to calculate days aged of emails sent to a particular email address. The source I'm using is SAP HANA and made a Live connection to it in Tableau. The field I have to work with is called Created At. This field holds the data in yyyymmddhhmmss string format.
The challenge: Convert the original date inCreated At field to a valid Working day and time (Monday-Friday and between 7:00am to 5:00pm). This is what I came up with:
CASE DATENAME('weekday',DATETIME([Created At]))
WHEN '7' THEN //item received on Saturday
DATEADD('day',2,DATEADD('hour',7,DATETRUNC('day',DATETIME([Created At]))))
//first, I cut off the original time from Created At with DATETRUNC.
//Next, I added 7 hours to get Saturday 7:00 am
//finally, I add 2 days to change the date from a Saturday to a Monday
WHEN '1' THEN //item received on Sunday
DATEADD('day',2,DATEADD('hour',7,DATETRUNC('day',DATETIME([Created At]))))
//first, I cut off the original time from Created At with DATETRUNC.
//Next, I added 7 hours to get Sunday 7:00 am
//finally, I add 1 day to change the date from a Sunday to a Monday
WHEN '6' THEN //item received on Friday
//item received before 7:00am (7hrs)
IF DATETIME([Created At]) < DATEADD('hour',7,DATETRUNC('day',DATETIME([Created At]))) THEN
//first I cut off the original time from Created At with DATETRUNC.
//Next, I added 7 hours to date to get the current date at 7:00 am
DATEADD('hour',7,DATETRUNC('day',DATETIME([Created At])))
//item received after 4:00pm (16hrs)
ELSEIF DATETIME([Created At]) > DATEADD('hour',16,DATETRUNC('day',DATETIME([Created At]))) THEN
DATEADD('day',3,DATEADD('hour',7,DATETRUNC('day',DATETIME([Created At]))))
//first I cut off the original time from Created At with DATETRUNC.
//Next, I added 7 hours to date to get the current date at 7:00 am
//finally, I add 3 days to change from Friday to Monday at 7:00 am
//item received between 7:00am and 4:00pm (7-16hrs)
ELSE DATETIME([Created At]) END
ELSE //received any other day of the week
//item received before 7:00am (7hrs)
IF DATETIME([Created At]) < DATEADD('hour',7,DATETRUNC('day',DATETIME([Created At]))) THEN
//first I cut off the original time from Created At with DATETRUNC.
//Next, I added 7 hours to date to get the current date at 7:00 am
DATEADD('hour',7,DATETRUNC('day',DATETIME([Created At])))
//item received after 4:00pm (16hrs)
ELSEIF DATETIME([Created At]) > DATEADD('hour',16,DATETRUNC('day',DATETIME([Created At]))) THEN
DATEADD('day',1,DATEADD('hour',7,DATETRUNC('day',DATETIME([Created At]))))
//first I cut off the original time from Created At with DATETRUNC.
//Next, I added 7 hours to date to get the current date at 7:00 am
//finally, I add 1 day to get next date forward at 7:00 am
//item received between 7:00am and 4:00pm (7-16hrs)
ELSE DATETIME([Created At]) END
END
Let me know if you have a better way to do this or let me know if you found it useful. Hope this helps!