How to count current week for the month?

Ezra

I'll have to adjust the formula that I had written to look at end-of-December dates so they look at beginning-of-January dates. Maybe it would be good to leave the end-of-December in there too, just in case..

Ahhhhhhh dang. OK, now I see what I was doing. Sorry, been a long week so far.

Sorry that I mis-explained the resultant of this formula.. it actually spits out the four digit year, a period, then a two digit week: YYYY.WW

And it is supposed to correct the year number if it's seeing that the week is 1 but the month is December.

I just ran a test of dates.. the first four are giving me correct numbers, but the fifth (January 1st of 2021) is not correct. So, I'll have to make an adjustment to subtract one from the year there.

**UPDATED FORMULA HERE**

=IFERROR(IF(AND(MONTH([Actual Start]@row) = 12, WEEKNUMBER([Actual Start]@row) = 1), YEAR([Actual Start]@row) + 1, IF(AND(MONTH([Actual Start]@row) = 1, WEEKNUMBER([Actual Start]@row) >50), YEAR([Actual Start]@row) - 1,YEAR([Actual Start]@row))) + "." + IF(WEEKNUMBER([Actual Start]@row) < 10, "0" + WEEKNUMBER([Actual Start]@row), WEEKNUMBER([Actual Start]@row)), "")

Paul Newcome

What about something like this...

We use the originally working formula:

=WEEKNUMBER(TODAY()) - WEEKNUMBER(DATE(YEAR(TODAY()), MONTH(TODAY()), 1)) + 1

But we start out with an IF to say that if the weeknumber of today is the same as the weeknumber of Dec. 31 of the previous year, then just output 1. That seems to be the only area causing issues is the year change and we already know that that week number should be 1.

=IF(WEEKNUMBER(TODAY()) = WEEKNUMBER(DATE(YEAR(TODAY()) - 1, 12, 31)), 1, WEEKNUMBER(TODAY()) - WEEKNUMBER(DATE(YEAR(TODAY()), MONTH(TODAY()), 1)) + 1)

What do y'all think about that?

Paul Newcome

When I replace TODAY with a date reference in the below, it doesn't work for me.

@Vivien Chong Are you sure this is working for you?

=WEEKNUMBER(TODAY()) - WEEKNUMBER(DATE(YEAR(TODAY()), MONTH(TODAY()), 1)) + 1

L_123

=ROUNDUP((WEEKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()), 1)) + DAY(TODAY())) / 7)

You can give this a try. I believe it gets rid of the issue.

Paul Newcome

@L@123 You beat me to it. Haha. I had that in a test sheet but then had a round of back to back calls come up before I could get it posted. I tested it with a handful of different dates throughout the year including Dec 31 and Jan 1, and it came out correct.

Ezra

I was just looking at the formula @L@123 just posted above... for some months that have a weekend falling in a previous month's week, it is being counted as the first week of the month in question. There may need to be some exceptions written into the formula, which is unfortunate, because I like how 'clean' the formula is. Unless you're not that picky :)

Here I applied the formula to a set of dates.. first column is the formula (changed it from looking at TODAY() to look at the date column) and the next is just a series of dates. As you see, 02/24/2020 gives you week number 5:

L_123

@Paul Newcome

Lol had to get you back for beating me before on this thread :)

L_123

@Ezra, that is expected behavior for this formula. There is a maximum potential of 6 weeks, the first of the month in your example is the only date in the first week of february.

Paul Newcome

@L@123 Haha. Fair enough.

@Ezra I agree with L@123. 5 would be the correct week number because based on earlier comments, the week is Monday through Sunday. So the 1st and 2nd being on Saturday and Sunday would be considered Week 1 and Monday the 3rd would be the start of Week 2.

Vivien Chong

thanks all @L@123 @Ezra for cracking your head to work on this formula, really appreciate it.

@Paul Newcome

I have tested this formula: =WEEKNUMBER(TODAY()) - WEEKNUMBER(DATE(YEAR(TODAY()), MONTH(TODAY()), 1)) + 1

so far, it's showing 1 because this week is really week 1, I think by next week I'll know if it works.

We just want to have it on the dashboard showing which week of the month it is, so it should be dynamic, and try to find a way to automate it instead of updating it every time.

If the formula doesn't work, I'll return here and seek assistance from all my sifu here. haha...

Have a blessed day!

Paul Newcome

@Vivien Chong

When I replaced TODAY() in the WEEKNUMBER() - WEEKNUMBER formula with dates (to see what would happen when TODAY() becomes those dates), I actually ended up getting a fair amount of incorrect outputs including some negative numbers.

I would actually suggest using @L@123's solution of:

=ROUNDUP((WEEKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()), 1)) + DAY(TODAY())) / 7)

Christian Ibarra

Another option. This is based on all possible scenarios. Because the formula was too large for a single Smartsheet cell had to be split into three.

Notice that this solution assumed Mondays as the start of the week.

First Column (only to connect the other two columns):

****IF(AND(DAY(Date@row) >= 1, DAY(Date@row) <= 13), [Week (for days 1-13)]@row, [Week (for days 14-31)]@row)

Second Column:

****=IF(DAY(Date@row) = 1, IF(AND(DAY(Date@row) = 1, WEEKDAY(Date@row) = 2), "W1", IF(AND(DAY(Date@row) = 1, WEEKDAY(Date@row) >= 3, WEEKDAY(Date@row) <= 4, (MONTH(Date@row) - 1) = 2, IFERROR(MONTH(DATE(YEAR(Date@row), MONTH(Date@row) - 1, 29)), 0) <> 2), "W4", IF(AND(DAY(Date@row) = 1, WEEKDAY(Date@row) >= 3, WEEKDAY(Date@row) <= 4), "W5", IF(AND(DAY(Date@row) = 1, WEEKDAY(Date@row) >= 6, WEEKDAY(Date@row) <= 7), "W4", IF(AND(DAY(Date@row) = 1, WEEKDAY(Date@row) = 1), "W4", IF(AND(DAY(Date@row) = 1, WEEKDAY(Date@row) = 5), IF(OR(MONTH(Date@row) = 2, MONTH(Date@row) = 4, MONTH(Date@row) = 6, MONTH(Date@row) = 9, MONTH(Date@row) = 11), "W5", "W4"), 0)))))), IF(DAY(Date@row) = 2, IF(AND(DAY(Date@row) = 2, WEEKDAY(Date@row) >= 2, WEEKDAY(Date@row) <= 3), "W1", IF(AND(DAY(Date@row) = 2, WEEKDAY(Date@row) >= 4, WEEKDAY(Date@row) <= 5, (MONTH(Date@row) - 1) = 2, IFERROR(MONTH(DATE(YEAR(Date@row), MONTH(Date@row) - 1, 29)), 0) <> 2), "W4", IF(AND(DAY(Date@row) = 2, WEEKDAY(Date@row) >= 4, WEEKDAY(Date@row) <= 5), "W5", IF(AND(DAY(Date@row) = 2, OR(WEEKDAY(Date@row) = 7, WEEKDAY(Date@row) = 1)), "W4", IF(AND(DAY(Date@row) = 2, WEEKDAY(Date@row) = 6), IF(OR(MONTH(Date@row) = 2, MONTH(Date@row) = 4, MONTH(Date@row) = 6, MONTH(Date@row) = 9, MONTH(Date@row) = 11), "W5", "W4"), 0))))), IF(DAY(Date@row) = 3, IF(AND(DAY(Date@row) = 3, WEEKDAY(Date@row) >= 2, WEEKDAY(Date@row) <= 4), "W1", IF(AND(DAY(Date@row) = 3, WEEKDAY(Date@row) >= 5, WEEKDAY(Date@row) <= 6, (MONTH(Date@row) - 1) = 2, IFERROR(MONTH(DATE(YEAR(Date@row), MONTH(Date@row) - 1, 29)), 0) <> 2), "W4", IF(AND(DAY(Date@row) = 3, WEEKDAY(Date@row) >= 5, WEEKDAY(Date@row) <= 6), "W5", IF(AND(DAY(Date@row) = 3, WEEKDAY(Date@row) = 1), "W4", IF(AND(DAY(Date@row) = 3, WEEKDAY(Date@row) = 7), IF(OR(MONTH(Date@row) = 2, MONTH(Date@row) = 4, MONTH(Date@row) = 6, MONTH(Date@row) = 9, MONTH(Date@row) = 11), "W5", "W4"), 0))))), IF(DAY(Date@row) = 4, IF(AND(DAY(Date@row) = 4, WEEKDAY(Date@row) >= 2, WEEKDAY(Date@row) <= 5), "W1", IF(AND(DAY(Date@row) = 4, WEEKDAY(Date@row) >= 6, WEEKDAY(Date@row) <= 7, (MONTH(Date@row) - 1) = 2, IFERROR(MONTH(DATE(YEAR(Date@row), MONTH(Date@row) - 1, 29)), 0) <> 2), "W4", IF(AND(DAY(Date@row) = 4, WEEKDAY(Date@row) >= 6, WEEKDAY(Date@row) <= 7), "W5", IF(AND(DAY(Date@row) = 4, WEEKDAY(Date@row) = 1), IF(OR(MONTH(Date@row) = 2, MONTH(Date@row) = 4, MONTH(Date@row) = 6, MONTH(Date@row) = 9, MONTH(Date@row) = 11), "W5", "W4"), 0)))), IF(DAY(Date@row) = 5, IF(AND(DAY(Date@row) = 5, WEEKDAY(Date@row) >= 2, WEEKDAY(Date@row) <= 6), "W1", IF(AND(DAY(Date@row) = 5, OR(WEEKDAY(Date@row) = 7, WEEKDAY(Date@row) = 1), (MONTH(Date@row) - 1) = 2, IFERROR(MONTH(DATE(YEAR(Date@row), MONTH(Date@row) - 1, 29)), 0) <> 2), "W4", IF(AND(DAY(Date@row) = 5, OR(WEEKDAY(Date@row) = 7, WEEKDAY(Date@row) = 1)), "W5", 0))), IF(DAY(Date@row) = 6, IF(AND(DAY(Date@row) = 6, WEEKDAY(Date@row) >= 2, WEEKDAY(Date@row) <= 7), "W1", IF(AND(DAY(Date@row) = 6, WEEKDAY(Date@row) = 1, (MONTH(Date@row) - 1) = 2, IFERROR(MONTH(DATE(YEAR(Date@row), MONTH(Date@row) - 1, 29)), 0) <> 2), "W4", "W5")), IF(DAY(Date@row) = 7, "W1", IF(DAY(Date@row) = 8, IF(AND(DAY(Date@row) = 8, WEEKDAY(Date@row) = 2), "W2", "W1"), IF(DAY(Date@row) = 9, IF(AND(DAY(Date@row) = 9, AND(WEEKDAY(Date@row) >= 2, WEEKDAY(Date@row) <= 3)), "W2", "W1"), IF(DAY(Date@row) = 10, IF(AND(DAY(Date@row) = 10, AND(WEEKDAY(Date@row) >= 2, WEEKDAY(Date@row) <= 4)), "W2", "W1"), IF(DAY(Date@row) = 11, IF(AND(DAY(Date@row) = 11, AND(WEEKDAY(Date@row) >= 2, WEEKDAY(Date@row) <= 5)), "W2", "W1"), IF(DAY(Date@row) = 12, IF(AND(DAY(Date@row) = 12, AND(WEEKDAY(Date@row) >= 2, WEEKDAY(Date@row) <= 6)), "W2", "W1"), IF(DAY(Date@row) = 13, IF(AND(DAY(Date@row) = 13, AND(WEEKDAY(Date@row) >= 2, WEEKDAY(Date@row) <= 7)), "W2", "W1"), 14)))))))))))))

Third Column:

****IF(DAY(Date@row) = 14, "W2", IF(DAY(Date@row) = 15, IF(AND(DAY(Date@row) = 15, WEEKDAY(Date@row) = 2), "W3", "W2"), IF(DAY(Date@row) = 16, IF(AND(DAY(Date@row) = 16, AND(WEEKDAY(Date@row) >= 2, WEEKDAY(Date@row) <= 3)), "W3", "W2"), IF(DAY(Date@row) = 17, IF(AND(DAY(Date@row) = 17, AND(WEEKDAY(Date@row) >= 2, WEEKDAY(Date@row) <= 4)), "W3", "W2"), IF(DAY(Date@row) = 18, IF(AND(DAY(Date@row) = 18, AND(WEEKDAY(Date@row) >= 2, WEEKDAY(Date@row) <= 5)), "W3", "W2"), IF(DAY(Date@row) = 19, IF(AND(DAY(Date@row) = 19, AND(WEEKDAY(Date@row) >= 2, WEEKDAY(Date@row) <= 6)), "W3", "W2"), IF(DAY(Date@row) = 20, IF(AND(DAY(Date@row) = 20, AND(WEEKDAY(Date@row) >= 2, WEEKDAY(Date@row) <= 7)), "W3", "W2"), IF(DAY(Date@row) = 21, "W3", IF(DAY(Date@row) = 22, IF(AND(DAY(Date@row) = 22, WEEKDAY(Date@row) = 2), "W4", "W3"), IF(DAY(Date@row) = 23, IF(AND(DAY(Date@row) = 23, AND(WEEKDAY(Date@row) >= 2, WEEKDAY(Date@row) <= 3)), "W4", "W3"), IF(DAY(Date@row) = 24, IF(AND(DAY(Date@row) = 24, AND(WEEKDAY(Date@row) >= 2, WEEKDAY(Date@row) <= 4)), "W4", "W3"), IF(DAY(Date@row) = 25, IF(AND(DAY(Date@row) = 25, AND(WEEKDAY(Date@row) >= 2, WEEKDAY(Date@row) <= 5)), "W4", "W3"), IF(DAY(Date@row) = 26, IF(AND(DAY(Date@row) = 26, AND(WEEKDAY(Date@row) >= 2, WEEKDAY(Date@row) <= 6)), "W4", "W3"), IF(DAY(Date@row) = 27, IF(AND(DAY(Date@row) = 27, AND(WEEKDAY(Date@row) >= 2, WEEKDAY(Date@row) <= 7)), "W4", "W3"), IF(DAY(Date@row) = 28, "W4", IF(DAY(Date@row) = 29, IF(AND(DAY(Date@row) = 29, WEEKDAY(Date@row) = 2), "W5", "W4"), IF(DAY(Date@row) = 30, IF(AND(DAY(Date@row) = 30, AND(WEEKDAY(Date@row) >= 2, WEEKDAY(Date@row) <= 3)), "W5", "W4"), IF(DAY(Date@row) = 31, IF(AND(DAY(Date@row) = 31, AND(WEEKDAY(Date@row) >= 2, WEEKDAY(Date@row) <= 4)), "W5", "W4"), 0))))))))))))))))))

Christian Ibarra

Second Column Correction:

****=IF(DAY(Date@row) = 1, IF(AND(DAY(Date@row) = 1, WEEKDAY(Date@row) = 2), "Wk1", IF(AND(DAY(Date@row) = 1, WEEKDAY(Date@row) >= 3, WEEKDAY(Date@row) <= 4, (MONTH(Date@row) - 1) = 2, IFERROR(MONTH(DATE(YEAR(Date@row), MONTH(Date@row) - 1, 29)), 0) <> 2), "Wk4", IF(AND(DAY(Date@row) = 1, WEEKDAY(Date@row) >= 3, WEEKDAY(Date@row) <= 4), "Wk5", IF(AND(DAY(Date@row) = 1, WEEKDAY(Date@row) >= 6, WEEKDAY(Date@row) <= 7), "Wk4", IF(AND(DAY(Date@row) = 1, WEEKDAY(Date@row) = 1), "Wk4", IF(AND(DAY(Date@row) = 1, WEEKDAY(Date@row) = 5), IF(OR(MONTH(Date@row) = 2, MONTH(Date@row) = 4, MONTH(Date@row) = 6, MONTH(Date@row) = 9, MONTH(Date@row) = 11), "Wk5", "Wk4"), 0)))))), IF(DAY(Date@row) = 2, IF(AND(DAY(Date@row) = 2, WEEKDAY(Date@row) >= 2, WEEKDAY(Date@row) <= 3), "Wk1", IF(AND(DAY(Date@row) = 2, WEEKDAY(Date@row) >= 4, WEEKDAY(Date@row) <= 5, (MONTH(Date@row) - 1) = 2, IFERROR(MONTH(DATE(YEAR(Date@row), MONTH(Date@row) - 1, 29)), 0) <> 2), "Wk4", IF(AND(DAY(Date@row) = 2, WEEKDAY(Date@row) >= 4, WEEKDAY(Date@row) <= 5), "Wk5", IF(AND(DAY(Date@row) = 2, OR(WEEKDAY(Date@row) = 7, WEEKDAY(Date@row) = 1)), "Wk4", IF(AND(DAY(Date@row) = 2, WEEKDAY(Date@row) = 6), IF(OR(MONTH(Date@row) = 2, MONTH(Date@row) = 4, MONTH(Date@row) = 6, MONTH(Date@row) = 9, MONTH(Date@row) = 11), "Wk5", "Wk4"), 0))))), IF(DAY(Date@row) = 3, IF(AND(DAY(Date@row) = 3, WEEKDAY(Date@row) >= 2, WEEKDAY(Date@row) <= 4), "Wk1", IF(AND(DAY(Date@row) = 3, WEEKDAY(Date@row) >= 5, WEEKDAY(Date@row) <= 6, (MONTH(Date@row) - 1) = 2, IFERROR(MONTH(DATE(YEAR(Date@row), MONTH(Date@row) - 1, 29)), 0) <> 2), "Wk4", IF(AND(DAY(Date@row) = 3, WEEKDAY(Date@row) >= 5, WEEKDAY(Date@row) <= 6), "Wk5", IF(AND(DAY(Date@row) = 3, WEEKDAY(Date@row) = 1), "Wk4", IF(AND(DAY(Date@row) = 3, WEEKDAY(Date@row) = 7), IF(OR(MONTH(Date@row) = 2, MONTH(Date@row) = 4, MONTH(Date@row) = 6, MONTH(Date@row) = 9, MONTH(Date@row) = 11), "Wk5", "Wk4"), 0))))), IF(DAY(Date@row) = 4, IF(AND(DAY(Date@row) = 4, WEEKDAY(Date@row) >= 2, WEEKDAY(Date@row) <= 5), "Wk1", IF(AND(DAY(Date@row) = 4, WEEKDAY(Date@row) >= 6, WEEKDAY(Date@row) <= 7, (MONTH(Date@row) - 1) = 2, IFERROR(MONTH(DATE(YEAR(Date@row), MONTH(Date@row) - 1, 29)), 0) <> 2), "Wk4", IF(AND(DAY(Date@row) = 4, WEEKDAY(Date@row) >= 6, WEEKDAY(Date@row) <= 7), "Wk5", IF(AND(DAY(Date@row) = 4, WEEKDAY(Date@row) = 1), IF(OR(MONTH(Date@row) = 2, MONTH(Date@row) = 4, MONTH(Date@row) = 6, MONTH(Date@row) = 9, MONTH(Date@row) = 11), "Wk5", "Wk4"), 0)))), IF(DAY(Date@row) = 5, IF(AND(DAY(Date@row) = 5, WEEKDAY(Date@row) >= 2, WEEKDAY(Date@row) <= 6), "Wk1", IF(AND(DAY(Date@row) = 5, OR(WEEKDAY(Date@row) = 7, WEEKDAY(Date@row) = 1), (MONTH(Date@row) - 1) = 2, IFERROR(MONTH(DATE(YEAR(Date@row), MONTH(Date@row) - 1, 29)), 0) <> 2), "Wk4", IF(AND(DAY(Date@row) = 5, OR(WEEKDAY(Date@row) = 7, WEEKDAY(Date@row) = 1)), "Wk5", 0))), IF(DAY(Date@row) = 6, IF(AND(DAY(Date@row) = 6, WEEKDAY(Date@row) >= 2, WEEKDAY(Date@row) <= 7), "Wk1", IF(AND(DAY(Date@row) = 6, WEEKDAY(Date@row) = 1, (MONTH(Date@row) - 1) = 2, IFERROR(MONTH(DATE(YEAR(Date@row), MONTH(Date@row) - 1, 29)), 0) <> 2), "Wk4", "Wk5")), IF(DAY(Date@row) = 7, "Wk1", IF(DAY(Date@row) = 8, IF(AND(DAY(Date@row) = 8, WEEKDAY(Date@row) = 2), "Wk2", "Wk1"), IF(DAY(Date@row) = 9, IF(AND(DAY(Date@row) = 9, AND(WEEKDAY(Date@row) >= 2, WEEKDAY(Date@row) <= 3)), "Wk2", "Wk1"), IF(DAY(Date@row) = 10, IF(AND(DAY(Date@row) = 10, AND(WEEKDAY(Date@row) >= 2, WEEKDAY(Date@row) <= 4)), "Wk2", "Wk1"), IF(DAY(Date@row) = 11, IF(AND(DAY(Date@row) = 11, AND(WEEKDAY(Date@row) >= 2, WEEKDAY(Date@row) <= 5)), "Wk2", "Wk1"), IF(DAY(Date@row) = 12, IF(AND(DAY(Date@row) = 12, AND(WEEKDAY(Date@row) >= 2, WEEKDAY(Date@row) <= 6)), "Wk2", "Wk1"), IF(DAY(Date@row) = 13, IF(AND(DAY(Date@row) = 13, AND(WEEKDAY(Date@row) >= 2, WEEKDAY(Date@row) <= 7)), "Wk2", "Wk1"), 14)))))))))))))

Paul Newcome

@Christian Ibarra I'd like to make a suggestion that could help drastically reduce the length of your formulas. You have some redundancies built in. Here's an example:

=IF(DAY(Date@row) = 1, IF(AND(DAY(Date@row) = 1, WEEKDAY(Date@row) = 2), "Wk1", IF(AND(DAY(Date@row) = 1, WEEKDAY(Date@row) >= 3,

Since you already have the

=IF(DAY(Date@row) = 1

You don't need to specify it in the AND statements.

=IF(DAY(Date@row) = 1, IF(WEEKDAY(Date@row) = 2, "Wk1", IF(AND(WEEKDAY(Date@row) >= 3,

Basically,

=IF(DAY(Date@row) = 1, IF(AND(DAY(Date@row) = 1, WEEKDAY(Date@row) = 2), "Wk1",

is the same as

=IF(DAY(Date@row) = 1, IF(WEEKDAY(Date@row) = 2, "Wk1",

which is also the same as this

IF(AND(DAY(Date@row) = 1, WEEKDAY(Date@row) = 2), "Wk1",

PeggyLang

@Paul Newcome
I find I am confused now. I had built a formula that I thought was working but turns out it is not.
Essentially I need to know what week of the month it is on any given Monday.
For Example, today is 07/29/24. Because the 1st of August is Thur of THIS week then this week is Week 1 of the 8th month.
I have 2 columns (Helpers).

One that calculates the month ~ =MONTH(Date)24; now this returns 7 which is incorrect.
Second column calculates week # ~ = WEEKNUMBER(Date)24) - WEEKENUMBER(DATE(YEAR(Date24), MONTH(Date24), 1)) + 1; this is also incorrect as it is returning 5 when it should return 1.

Can you help?