# 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 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 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 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 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 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 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.

## Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

×   Pasted as rich text.   Paste as plain text instead

Only 75 emoji are allowed.

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×

• ### Who Viewed the Topic

1 member has viewed this topic:
Terrible Toll
×
×
• Create New...