Jan 09, 2018 05:54 PM
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…
Jan 09, 2018 06:40 PM
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))
Jan 10, 2018 02:47 AM
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
Jan 10, 2018 05:35 AM
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}
.
Jun 28, 2019 05:35 PM
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.
Jun 28, 2019 06:55 PM
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.
Jul 09, 2019 06:11 PM
@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?