Week starting with Friday

[TROY_Perrault]

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…

[W_Vann_Hall]

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))

[TROY_Perrault]

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

[W_Vann_Hall]

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

[The_Dan_Smock]

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.

[W_Vann_Hall]

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]

@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?