Calculate month/year to date with one date field

[DaK888]

Hopefully I can explain what I'm trying to accomplish...

See current spreadsheet screenshot attached.

I'm trying to show a comparison for data from 2024 vs 2025.

Calculating straight mtd/ytd with complete months I can do. However, we need to submit this report at the end of every month, but also in the middle of the month and that's where I'm having an issue.

I want to use the date field in b2 which would be manually entered to indicate how current the data is for 2025, to power the data displayed in column z. IE, I want to prorate the data in column z, based on how many days we are reporting for the current year.

In this case, we are looking to prorate March 2024 to March 25th, 2025 (I know, not mid month but this is for my testing). So in this case, we would want to sum Jan-Feb 2024 numbers and then add in prorated March 2024 to match what we have for 2025.

Does that make sense?

Any help is appreciated.

Thanks!

 

[Sam_D_Ben]

Something like this,

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		Data Date	Margin
2		3/25/2025	27%
3
4
5		January		February		March		YTD		Totals
6		2024	2025	2024	2025	2024	2025	2024	2025	2024	2025	2024 Prorate
7		720	971	636	819	689	639	1,618	2,429	7,115	2,429	1,912
8		29	23	30	25	29	20	58	68	257	68	82
9		6	5	8	5	6	3	13	13	56	13	19
10		$24,822.26	$49,019.88	$25,323.71	$53,356.10	$38,679.60	$41,168.15	$84,082.14	$143,544.13	$369,758.81	$143,544.13	$81,339.20
11		$34.48	$50.48	$39.82	$65.15	$56.14	$64.43	$51.97	$59.10	$51.97	$59.10	$119.57
Sheet3

Cell Formulas
Range	Formula
L7:L11	L7	=B7 + D7 + (F7 * DAY($B$2) / DAY(EOMONTH($B$2, 0)))

 

[Alex Blakenburg]

Firstly what version of Excel are you using ? Please update your Account profile to show the version since the solution often depends on the version.
Secondly if you have to use an image please include both the Row and Column references.
Your YTD figures for 2024 don't seem to show the expected result.

See if this works for you in Z6. The figures it calculates agree to @Sam_D_Ben's results but the formula is more generic.
I suspect someone can come up with a cleaner version.

=SUMPRODUCT(($B$5:$Y$5=Z$5)
            * (--($B$4:$Y$4&"-"&$B$5:$Y$5)<(EOMONTH($B$2,-13)+1))
            * $B6:$Y6)
  + (DAY($B$2)/DAY(EOMONTH($B$2,0))*INDEX($B6:$Y6,0,MONTH($B$2)*2-1))

 

[Sam_D_Ben]

Or Something like this,

Book1
	B	C	D	E	F	G	H	I	J	K	L
1	Data Date	Margin
2	3/25/2025	27%
3
4
5	January		February		March		YTD		Totals
6	2024	2025	2024	2025	2024	2025	2024	2025	2024	2025	2024 Prorate
7	720	971	636	819	689	639	1,618	2,429	7,115	2,429	2,346
8	29	23	30	25	29	20	58	68	257	68	71
9	6	5	8	5	6	3	13	13	56	13	15
Sheet1

Cell Formulas
Range	Formula
L7:L9	L7	=SUM(FILTER(B7:F7,($B$6:$F$6=YEAR($B$2))*(COLUMN($B$6:$F$6)-COLUMN(B6)+1<=(MONTH($B$2)-1)*2)))+(DAY($B$2)/DAY(EOMONTH($B$2,0)))*INDEX(B7:F7,,(MONTH($B$2)-1)*2+1)

 

[kvsrinivasamurthy]

Timesheet.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1		Data Date	Margin
2		25-03-2025	0.27
3
4
5		January	January	February	February	March	March	YTD		Totals			Prorate for date in B2 for year
6		2024	2025	2024	2025	2024	2025	2024	2025	2024	2025		2024	2025
7		720	971	636	819	689	639	1618	2429	7115	2429		1911.6452	2345.6452
8		29	23	30	25	29	20	58	68	257	68		82.3871	71.3871
9		6	5	8	5	6	3	13	13	56	13		18.8387	14.8387
10		24822.26	49019.88	25323.71	53356.1	38679.6	41168.15	84082.14	143544.1	369758.8	143544.1		81339.1958	133569.2058
11		34.48	50.48	39.82	65.15	56.14	64.43	51.97	59.1	51.97	59.1		119.5742	160.9042
12
sheet1

Cell Formulas
Range	Formula
M7:N11	M7	=SUM(IF(($B$6:$G$6=M$6)*(MONTH($B$5:$G$5&0)<MONTH($B$2)),($B7:$G7)*($B$6:$F$6=M$6),IF(($B$6:$G$6=M$6)*(MONTH($B$5:$G$5&0)=MONTH($B$2)),INDEX($B7:$G7,MATCH(MONTH($B$2),MONTH($B$5:$G$5&0),0))*DAY($B$2)/DAY(EOMONTH($B$2,0)),0)))
Press CTRL+SHIFT+ENTER to enter array formulas.

PL note the change in Month row (Row 5).
ARRAY formula (Ctrl+shift+enter) in M7 copied up to N11. Any date from 1st jan to 31 mar can be used. Comparative for that date for 2024 and 2025 is given.

=SUM(IF(($B$6:$G$6=M$6)*(MONTH($B$5:$G$5&0)<MONTH($B$2)),($B7:$G7)*($B$6:$F$6=M$6),IF(($B$6:$G$6=M$6)*(MONTH($B$5:$G$5&0)=MONTH($B$2)),INDEX($B7:$G7,MATCH(MONTH($B$2),MONTH($B$5:$G$5&0),0))*DAY($B$2)/DAY(EOMONTH($B$2,0)),0)))

 

[DaK888]

Firstly what version of Excel are you using ? Please update your Account profile to show the version since the solution often depends on the version.
Secondly if you have to use an image please include both the Row and Column references.
Your YTD figures for 2024 don't seem to show the expected result.

See if this works for you in Z6. The figures it calculates agree to @Sam_D_Ben's results but the formula is more generic.
I suspect someone can come up with a cleaner version.

=SUMPRODUCT(($B$5:$Y$5=Z$5)
            * (--($B$4:$Y$4&"-"&$B$5:$Y$5)<(EOMONTH($B$2,-13)+1))
            * $B6:$Y6)
  + (DAY($B$2)/DAY(EOMONTH($B$2,0))*INDEX($B6:$Y6,0,MONTH($B$2)*2-1))

My apologies! Long time member but haven't posted in years so my stuff is a bit out of date. Updated now! And I guess I wasn't aware of the excel embed tool, at least that I remember... Anyway, this solution worked perfectly! Thank you!

 

[DaK888]

Thanks everyone. Ultimately Alex's version worked the best for me but I appreciate everyone pitching in!