+ Start a Discussion
the1castdownthe1castdown 

Custom Formula Fields for Assignment Rules Based on Day of the Week and Case Creation Date

I created 2 custom formula fields, one for calculating the case creation time as a number and one for calculating the day of the week in which the case was created.  I needed these to create assignment rules based on time of day and day of the week.
 
The Creation Time is calculated in Military time, because the formula I wrote to figure out the standard time was too many characters. This is the formula for a Custom Field that returns numbers with 2 decimal places:
 
VALUE (TEXT (IF (
VALUE (MID (TEXT (CreatedDate),12,2)) - 7 <= 0,
VALUE (MID (TEXT (CreatedDate),12,2)) - 7 + 24,
VALUE (MID (TEXT (CreatedDate),12,2)) - 7
)) & "." & MID (TEXT (CreatedDate),15,2))
 
My concern is that this formula won't be correct when we leave daylight savings time.  Any ideas how to take daylight savings into consideration? 
 
For the day of the week, I used I function I found somewhere on the dev network.  It looks like this:
 
CASE(
MOD( TODAY() - DATE(1900, 1, 7), 7),
0, "Sunday",
1, "Monday",
2, "Tuesday",
3, "Wednesday",
4, "Thursday",
5, "Friday",
6, "Saturday", "Error")
 
I guess I'm looking for a solution to the daylight savings time problem, or a better way to create assignment rules based on day of the week and time of day.
BluIdTi2grBluIdTi2gr

Did you ever find an answer for the daylight savings issue?  I needed this exact calculation of day of week and time of day cases were received to do queue based routing.  If you got your daylight savings issue resolved and I can use your formulas - you just saved me hours of time!!!!!

 

Thanks!

Jean