Formula Help

mromaire

How do I need to write this formula to return the date for Week Day; "Mon" for each week of the year based on the current year?

Thanks

Carson Penticuff

If I understand your question correctly, this will return the date of the Monday of the current workweek.

=IF(ISDATE([Date]@row), IF(WEEKDAY([Date]@row) = 1, [Date]@row - 6, [Date]@row - (WEEKDAY([Date]@row) - 2)), "")

mromaire

This formula returns the Monday date which I need, but I want it to only return the Monday date next to the adjacent 'Week' number column. So the Monday date for the first week of the year then the 2nd week and so on through all 52 weeks of the year.

Thanks for the assistance Carson.

DKazatsky2

Hi @mromaire,

One thing I would point out is that with the above formula, it always goes to the previous Monday. What this means is that if the first day of the year is a Sunday, like it is in your example, it would report the Monday as 12/26/22. If the formula is changed to add one day if it's Sunday, instead of subtracting 6 days, it looks forward instead of back.

=IF(ISDATE([Date]@row), IF(WEEKDAY([Date]@row) = 1, [Date]@row + 1, [Date]@row - (WEEKDAY([Date]@row) - 2)), "")

This will result in the first Monday being 1/2/2023, which I believe is what you are looking for?

The above formula can be put in the in the date field for week number 1 and then the following formula can be be put in week 2 and dragged down to week 52.

=[The Date]1 + 7

Hope this helps,

Dave

mromaire

This doesn't work correctly either. I must not be explaining what I need correctly.

If the week# is week@row and Weekday is "Mon" then return date.

Carson Penticuff

Ah, I think I understand now. Given the year, week number, and day of the week, you want to know the actual date?

If this is the case, what day would your week start? For example 01/01/2023 was on Sunday. If your week starts on Sunday, then Sunday of the first week of 2023 would be 01/01/2023. If your week starts on Monday, then (at least typically) Sunday of the first week of 2023 would be 01/08/2023 and 01/01/2023 would be considered Sunday of the last week of 2022.

mromaire

To answer your first question. The actual date for the Mon of that year for the week number.

The week starts with Monday. So for 2023 Mon was 1/2/23 for WK 1. WK2 will be Mon 1/9/23 and so on. Now when the year changes to 2024 it should accordingly.

I have a formula in the year column to update the year based on Today.

=YEAR(TODAY())

In the Date column I have this formula to package the date together to show in date format. So as the year updates so to does the date column which then updates the Weekday column to the correct day of the week.

In Weekday column;

=IFERROR(IF(WEEKDAY(Date@row) = 1, "Sun", IF(WEEKDAY(Date@row) = 2, "Mon", IF(WEEKDAY(Date@row) = 3, "Tues", IF(WEEKDAY(Date@row) = 4, "Wed", IF(WEEKDAY(Date@row) = 5, "Thurs", IF(WEEKDAY(Date@row) = 6, "Fri", IF(WEEKDAY(Date@row) = 7, "Sat"))))))), " ")

So knowing this I want The Date column to show the date on Mon for weeks 1-52 and it should automatically update every year.

If the Week (#) matches the Week # and the Weekday is "Mon" then return date.

DKazatsky2

This statement "If the Week (#) matches the Week # and the Weekday is "Mon" then return date" will never be true if I am looking at your example properly. In the "Week" column, doesn't it stop at 52? I don't see how these other columns will ever match.

In the example I gave, it gets the Monday of week 1 and then every Monday date following - just like what you show in your example (So for 2023 Mon was 1/2/23 for WK 1. WK2 will be Mon 1/9/23 and so on.)

Maybe you should mock up something with dummy data filled in to show us exactly what you are looking to acheive.