When Jan1 is a Sunday, particularly if it is a Sunday on Smartsheet week 52 (smartsheet 1/1/23), that's a special case- At least for the path I was heading, which is where I stopped. This requires the Smartsheet Adjusted Weeknumbers be offset anywhere from 0 - 2 weeks to match with Outlook. Outlook makes whatever week Jan1 falls in as week1, regardless of what day of the week it is. =IF(WEEKNUMBER([Start + [ADJ > 52, WEEKNUMBER([Start + [ADJ - 52, WEEKNUMBER([Start + [ADJ me, the harder part is My = IF(WEEKDAY([Start = 1, 1, 0) (This is a Text/Number column) This eliminated having to always check within the formula for Sundays, which on my path saved alot of IFs. I added a helper column called Adj Day to look for Sundays, and added a 1 when it was a Sunday, zero for everything else. I always welcome learning/ building with others.Īs I looked at Outlook weeknumbers vs Smartsheet weeknumbers, here's what I observedĪs you said, Outlook uses Sun as Day 1. You're in luck that Cronk is already weighing in.
#Formula for week number in excel full#
The community is full of clever members and I'll shout out to a few that I have seen do very ingenious things specifically with dates- not intentionally leaving anyone out.
However making the formula robust enough for any year, at least on the path I started, is complicated.
We can easily manage weeknumbers for 2021- which is initially what I thought was needed. I didn't want you to think I had stopped working.
0 kommentar(er)
