Jump to content
Jeff M

Day of Week function needed

Recommended Posts

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

Share this post


Link to post
Share on other sites

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. 

Share this post


Link to post
Share on other sites

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 )

)

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
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 by comment

Share this post


Link to post
Share on other sites
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. :)

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Who Viewed the Topic

    1 member has viewed this topic:
    arni 
×

Important Information

By using this site, you agree to our Terms of Use.