Day of Week function needed

[Jeff M]

I'm working on a time clock solution and I could use an elegant way of calculating the day of the week based on a user preference of when they start their work week. Some employers start their work week on Sunday, some on Monday, etc. The built in day of week function is based on Sunday being the first day. 

I created a preference where the user sets the day of week they want their pay periods to start on, for example 2 for Monday.

My goal is be able to take a timestamp, hand it to a custom function and get back the "adjusted" day of week. So a timestamp on Monday would return 1, Tuesday would return 2, etc. If they set the work week to start on Tuesday, then a Tuesday timestamp would return 1, Wednesday 2, etc. 

I know I could just create a lookup table and build out all the days with the FileMaker day of week and the new Adjusted day of week, but a function would be so much cleaner.

Thoughts? Suggestions?

-Jeff

[ron G]

try Briandunning.com

Perhaps this:  http://www.briandunning.com/cf/355

[Jeff M]

Ron, I haven't had any luck there. I post custom functions there and search their regularly. Love that site. However, I've not found anything that does what I need yet. 

[Jeff M]

May have come up with something... would love some feedback:

newStartDOW will be the adjusted DOW start

myTimestamp will be the timestamp handed to the function

 

Case ( 

newStartDOW = 2 ; DayOfWeek ( myTimestamp - 1 ) ;
newStartDOW = 3 ; DayOfWeek ( myTimestamp - 2 ) ;
newStartDOW = 4 ; DayOfWeek ( myTimestamp - 3 ) ;
newStartDOW = 5 ; DayOfWeek ( myTimestamp - 4 ) ;
newStartDOW = 6 ; DayOfWeek ( myTimestamp - 5 ) ;
newStartDOW = 7 ; DayOfWeek ( myTimestamp - 6 )

)

[comment]

You could do simply:

DayofWeek ( GetasDate ( StartTimestamp ) - WeekStartDay + 1 )

WeekStartDay is expected to be a number between 1 (Sunday) and 7 (Saturday). The result will be a number between 1 and 7 representing the number of the day of StartTimestamp in the week that starts on WeekStartDay.

[Jeff M]

That may just work and even more simple than my thoughts. Thanks!

Yes, that works! Sometimes you just know your overthinking things and need a second opinion. You rock.

 

Jeff

[comment]

29 minutes ago, Jeff M said:

Case ( 

newStartDOW = 2 ; DayOfWeek ( myTimestamp - 1 ) ;
newStartDOW = 3 ; DayOfWeek ( myTimestamp - 2 ) ;
newStartDOW = 4 ; DayOfWeek ( myTimestamp - 3 ) ;
newStartDOW = 5 ; DayOfWeek ( myTimestamp - 4 ) ;
newStartDOW = 6 ; DayOfWeek ( myTimestamp - 5 ) ;
newStartDOW = 7 ; DayOfWeek ( myTimestamp - 6 )

)

Note that the expression:

myTimestamp - 1 

takes 1 second off the timestamp value - so that the day of week will remain the same no matter what the value of newStartDOW is (unless the timestamp is only a few seconds after midnight).

 

 

Edited January 8, 2017 by comment

[Jeff M]

Just now, comment said:

Note that the expression:

myTimestamp - 1 

takes 1 second off the timestamp value - so that the day of week will remain the same no matter the value of newStartDOW is (unless the timestamp is only a few seconds after midnight).

Yes I noticed this just after I posted. I needed to isolate the date then subtract 1.