Split weekly time card data into monthly totals

[excel-er-8]

Hi all,

I have the following weekly time card data. I'm looking for a formula for the Jan 21 thru Apr 21 columns to total the portion of the hours that belong to each month. (I've pre-filled Jan 21 thru Apr 21 with what I expect the results to be from a formula.)

Hoping someone has an idea for this puzzle. Thank you!

Week starts on	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Jan 21	Feb 21	Mar 21	Apr 21
2021-01-03​	0.00​	8.00​	8.00​	8.00​	8.00​	8.00​	0.00​	40.00​
2021-01-24​	0.00​	8.00​	8.00​	8.00​	8.00​	8.00​	0.00​	40.00​
2021-01-31​	8.00​	8.00​	8.00​	8.00​	8.00​	0.00​	0.00​	8.00​	32.00​
2021-02-07​	8.00​	8.00​	8.00​	8.00​	8.00​	0.00​	0.00​		40.00​
2021-02-28​	8.00​	8.00​	8.00​	8.00​	8.00​	0.00​	0.00​		8.00​	32.00​
2021-03-07​	0.00​	8.00​	8.00​	8.00​	8.00​	8.00​	0.00​			40.00​
2021-03-28​	8.00​	8.00​	8.00​	8.00​	8.00​	0.00​	0.00​			8.00​	32.00​

 

[vmjan02]

Try this
I have added a column after Date as month and have enter the formula as =TEXT(A2,"MMM") column B as month and formula in cell b2.
and then have enter the formula as
=IF(J$1=$B2,SUM($C2:$I2),"") for Jan column J2
=IF(K$1=$B2,SUM($C2:$I2),"") for Feb Column K2
=IF(L$1=$B2,SUM($C2:$I2),"") for Mar column L2

Date​	Month​	Sunday​	Mon​	Tue​	Wed​	Thu​	Fri​	Sat​	Jan​	Feb​	Mar​
2021-01-03​	Jan​	0.00​	8.00​	0.00​	8.00​	0.00​	0.00​	0.00​	16.00​
2021-01-24​	Jan​	0.00​	8.00​	0.00​	8.00​	0.00​	0.00​	0.00​	16.00​
2021-01-31​	Jan​	8.00​	8.00​	8.00​	8.00​	8.00​	0.00​	0.00​	40.00​
2021-02-07​	Feb​	8.00​	8.00​	8.00​	8.00​	8.00​	0.00​	0.00​		40.00​
2021-02-28​	Feb​	8.00​	8.00​	8.00​	8.00​	8.00​	8.00​	8.00​		56.00​
2021-03-07​	Mar​	0.00​	0.00​	8.00​	0.00​	0.00​	0.00​	0.00​			8.00​
2021-03-28​	Mar​	0.00​	0.00​	8.00​	0.00​	0.00​	0.00​	0.00​			8.00​

 

[Toadstool]

Hi Excel-er-8,

If you'll let me have a work row then SUMIFS will do it (P.S. I believe you have the March/April for 28 March switched in your worked example).

Excel-er-8.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1		0	1	2	3	4	5	6
2	Week starts on	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Jan-21	Feb-21	Mar-21	Apr-21
3	03-Jan-21	0	8	8	8	8	8	0	40
4	24-Jan-21	0	8	8	8	8	8	0	40
5	31-Jan-21	8	8	8	8	8	0	0	8	32
6	07-Feb-21	8	8	8	8	8	0	0		40
7	28-Feb-21	8	8	8	8	8	0	0		8	32
8	07-Mar-21	0	8	8	8	8	8	0			40
9	28-Mar-21	8	8	8	8	8	0	0			32	8
Sheet1

Cell Formulas
Range	Formula
J2:L2	J2	=EOMONTH(I2,0)+1
I3:L9	I3	=SUMIFS($B3:$H3,$B$1:$H$1,">="&I$2-$A3,$B$1:$H$1,"<="&EOMONTH(I$2,0)-$A3)

 

[excel-er-8]

Hi Excel-er-8,

If you'll let me have a work row then SUMIFS will do it (P.S. I believe you have the March/April for 28 March switched in your worked example).

Excel-er-8.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1		0	1	2	3	4	5	6
2	Week starts on	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Jan-21	Feb-21	Mar-21	Apr-21
3	03-Jan-21	0	8	8	8	8	8	0	40
4	24-Jan-21	0	8	8	8	8	8	0	40
5	31-Jan-21	8	8	8	8	8	0	0	8	32
6	07-Feb-21	8	8	8	8	8	0	0		40
7	28-Feb-21	8	8	8	8	8	0	0		8	32
8	07-Mar-21	0	8	8	8	8	8	0			40
9	28-Mar-21	8	8	8	8	8	0	0			32	8
Sheet1

Cell Formulas
Range	Formula
J2:L2	J2	=EOMONTH(I2,0)+1
I3:L9	I3	=SUMIFS($B3:$H3,$B$1:$H$1,">="&I$2-$A3,$B$1:$H$1,"<="&EOMONTH(I$2,0)-$A3)

That's it! And yes, i had March/April switched. Thanks so much!

 

[Toadstool]

You're welcome!
Thanks for the feedback.