Help

Week starting with Friday

3721 6
cancel
Showing results for 
Search instead for 
Did you mean: 
TROY_Perrault
4 - Data Explorer
4 - Data Explorer

im looking to configure a column for “week” using weeknum(date) but i need my week to start on friday (first day of our pay period). i thought (date-3) might work but i just get an error… please help…

6 Replies 6

In part it depends on how you wish to shift the week, I’m assuming you’d want the number of the week Sunday-to-Saturday week that begins two days after pay period Friday, so you’d want to add days, not subtract them — but you wouldn’t want to add days to jut any date. See if this gets you what you want:

IF(WEEKDAY(Date)>4,WEEKNUM(DATEADD(Date,2,'days')),WEEKNUM(Date))

thanks W_Vann_Hall https://community.airtable.com/u/w_vann_hall
appears to work perfectly although i dont completely follow the formula.
regardless, i plugged it in and it works… thx again… TP

Yeah, I guess I left off the last paragraph of that reply… :winking_face:

  • IF(WEEKDAY(Date)>4

If {Date} is a Friday or Saturday…

  • WEEKNUM(DATEADD(Date,2,'days'),

…add two days to {Date}, ‘pushing’ Friday and Saturday into what would be the following week (or, inversely, moving the start of the week back two days to Friday); return WEEKNUM() for that week.

  • WEEKNUM(Date))

Otherwise (that is, the day of {Date} is Sunday through Thursday), return WEEKNUM() for the unchanged {Date}.

Could this be used somehow to calculate a different work week? We currently work here on a Sunday-to-Thursday week, and trying to figure out how to calculate PTO using WORKDAY_DIFF() (or some variant thereof). Because right now if someone’s PTO crosses a Sunday, for example, the WORKDAY formula doesn’t count that against the total.

At some point before WORKDAY() and WORKDAY_DIFF() were released, I put together some code for someone to make similar calculations (without the support for holidays, alas — but I’ve since figured out how to jigger WORKDAY() accordingly). It might be of use to you… and it’s somewhere in one of the 300+ bases and 1200+ messages in my account. I’ll be traveling Sunday and preparing to travel tomorrow, so I’m not sure when I’ll have a chance to search for it — but if you don’t find an answer and haven’t heard back from me by, oh, Tuesday, ping me here to remind me.

The_Dan_Smock
4 - Data Explorer
4 - Data Explorer

@W_Vann_Hall I couldn’t find it in your body of work…so pinging you here to see if I could take a look at what you think might work?