MTD Formula not working

[tourless]

Hi Folks.

I'm working on a report that produces three datasets, week-to-date, month-to-date, and year-to-date. I can properly calculate the week start and end dates, but the MTD start date value is not claculating properly.

As an example, my cell C7 has a value of 1/6/2024 and =IF(WEEKDAY(C7)<>1, C7-WEEKDAY(C7-1), A1-WEEKDAY(C7,1)-7) correctly returns 12/21/2023 as the start date for the MTD range. If I change the value in C7 to 1/13/2024 my formula returns 1/7/2024 where it should again return 12/31/2023 as the MTD start date. I've tried a fw variations on the formula but I'm just not getting it. Can anyone help me see what I'm doign wrong?

 

[awoohaw]

what is in cell A1?

Book1
	A	B	C	D	E
1
2
3
4
5
6
7				2024-01-06	#NUM!
8
Sheet1

Cell Formulas
Range	Formula
E7	E7	=IF(WEEKDAY(C7)<>1, C7-WEEKDAY(C7-1), A1-WEEKDAY(C7,1)-7)

Here are some ways that I would do this, but not really sure how you want to handle what day of week is the start day? I assume Mondays.

Book1
	A	B	C	D	E	F	G	H
1
2
3
4
5						Asumming Mondays
6					Your Formula	WTD Start Date	MTD Start Date	YTD Start Date
7				2024-01-06	#NUM!	Monday 2024-01-01	Monday 2024-01-01	Monday 2024-01-01
8				2024-01-09		Monday 2024-01-08	Monday 2024-01-01	Monday 2024-01-01
9				2024-01-12		Monday 2024-01-08	Monday 2024-01-01	Monday 2024-01-01
10				2024-01-15		Monday 2024-01-15	Monday 2024-01-01	Monday 2024-01-01
11				2024-01-18		Monday 2024-01-15	Monday 2024-01-01	Monday 2024-01-01
12				2024-01-21		Monday 2024-01-15	Monday 2024-01-01	Monday 2024-01-01
13				2024-01-24		Monday 2024-01-22	Monday 2024-01-01	Monday 2024-01-01
14				2024-01-27		Monday 2024-01-22	Monday 2024-01-01	Monday 2024-01-01
15				2024-01-30		Monday 2024-01-29	Monday 2024-01-01	Monday 2024-01-01
16				2024-02-02		Monday 2024-01-29	Thursday 2024-02-01	Monday 2024-01-01
17				2024-02-05		Monday 2024-02-05	Thursday 2024-02-01	Monday 2024-01-01
18				2024-02-08		Monday 2024-02-05	Thursday 2024-02-01	Monday 2024-01-01
19				2024-02-11		Monday 2024-02-05	Thursday 2024-02-01	Monday 2024-01-01
20				2024-02-14		Monday 2024-02-12	Thursday 2024-02-01	Monday 2024-01-01
21				2024-02-17		Monday 2024-02-12	Thursday 2024-02-01	Monday 2024-01-01
Sheet1

Cell Formulas
Range	Formula
E7	E7	=IF(WEEKDAY(C7)<>1, C7-WEEKDAY(C7-1), A1-WEEKDAY(C7,1)-7)
F7:F21	F7	=WORKDAY.INTL(D7+1,-1,"0111111")
G7:G21	G7	=EOMONTH(D7,-1)+1
H7:H21	H7	=DATE(YEAR(D7),1,1)

 

[tourless]

nothing, just a typo. it should read as IF(WEEKDAY(C7)<>1, C7-WEEKDAY(C7-1), C7-WEEKDAY(C7,1)-7). Still doesn't solve my problem though. I'm dealing with start and end dates where end dats is a given saturday and the start date is the prior sunday. For an end date of 1/6/2024 my WTD and MTD and YTD start dates should all be 12/31/2023. For an end date of 1/13/2024, my WTD start date should be 1/7/2024, my MTD start date should be 12/31/2023, and my YTD start date should be 12/31/2024.
To expand that forward an end date of 3/2/2024 should have WTD start date of 2/25/2024, and MTD start date of 2/25/2024, and a YTD start date of 12/31/2024. An end date of 3/23/2024 should have a WTD start date of 3/17/2024, a MTD start date of 2/25/2024, and a YTD start date of 12/31/2024.
There is a pattern there but I just don't know how to formulate those calculations. Basically we run our weeks Sunday to Saturday with Saturday being the last date of any given week. If that week's sunday falls within the prior month it's still part of the calculation for that given month.

 

[awoohaw]

So, the first day of week to date is always a SUNDAY?
The first day of a month is the actual last calendar day of the prior month, but if the last day of the calendar month is a Sunday, the first day of that month is the 1st?
(How does that affect your week to date end date of prior week, and the next week?)
What if the last calendar day of the year is a sunday (Sunday 12/31/####, do you then want the the first day of the year to be 1/1/####=+1?

 

[tourless]

the first day of a week is always sunday. in the case of this year, 1/6/24 is the first saturday of January making the first day of the week and month 12/31/23. 2/3/24 is the first saturday of February making the first day of the week and month 1/28/24. 1/13/, 1/20, and 1/27 all have MTD start dates of 12/31/24. 2/3, 2/10, 2/17, and 2/24 all have MTD start dates of 1/28/24.

 

[tourless]

I think I got it...
=DATE(YEAR(C7),MONTH(C7),1)+(7-WEEKDAY(DATE(YEAR(C7),MONTH(C7),1)))
returns the date of the first saturday of the given month in C7 as a helper cell, then I take that value and subtract 6 to get my starting sunday.

 

[awoohaw]

Okay. I htink you have some typos in post #5 where you say MTD start date of 12/31/2024, should it be 2023? But, if you got it working, great. But, you could get away with helper cells by using the Let function.