Week to Date / Month to Date Calculations (by providing only a single date)

[kmaxx98]

Good afternoon,

Hoping someone out there has a nice offset formula to help me out here in excel 2010.

I have an annual list of data by date. If I provide only one date from a drop down menu, is there a way for it to recognize the first Monday of that week and sum all values from that Monday?

Likewise, and probably easier, is there a way to get it to recognize the first day of the month, and sum from there?

For the example below, the desired output would be:

WTD=15
MTD=21

Data</SPAN>				Input</SPAN>		Output</SPAN>
Date</SPAN>	Day</SPAN>	Value</SPAN>		Work Date</SPAN>		WTD Sum</SPAN>	MTD Sum</SPAN>
29-May</SPAN>	Tue</SPAN>	10</SPAN>		6-Jun</SPAN>		??</SPAN>	??</SPAN>
30-May</SPAN>	Wed</SPAN>	15</SPAN>
31-May</SPAN>	Thu</SPAN>	16</SPAN>
1-Jun</SPAN>	Fri</SPAN>	1</SPAN>
2-Jun</SPAN>	Sat</SPAN>	2</SPAN>
3-Jun</SPAN>	Sun</SPAN>	3</SPAN>
4-Jun</SPAN>	Mon</SPAN>	4</SPAN>
5-Jun</SPAN>	Tue</SPAN>	5</SPAN>
6-Jun</SPAN>	Wed</SPAN>	6</SPAN>
7-Jun</SPAN>	Thu</SPAN>	7</SPAN>
8-Jun</SPAN>	Fri</SPAN>	8</SPAN>
9-Jun</SPAN>	Sat</SPAN>	9</SPAN>

<TBODY>
</TBODY><COLGROUP><COL span=3><COL><COL><COL><COL><COL></COLGROUP>

 

[AhoyNC]

See if this works for you:

Excel Workbook
	A	B	C	D	E	F	G	H
1	Date	Day	Value		Work Date		WTD Sum	MTD Sum
2	29-May	Tue	10		6-Jun		15	21
3	30-May	Wed	15
4	31-May	Thu	16
5	1-Jun	Fri	1
6	2-Jun	Sat	2
7	3-Jun	Sun	3
8	4-Jun	Mon	4
9	5-Jun	Tue	5
10	6-Jun	Wed	6
11	7-Jun	Thu	7
12	8-Jun	Fri	8
13	9-Jun	Sat	9
14	10-Jun	Sun	10
15	11-Jun	Mon	11
16	12-Jun	Tue	12
Sheet

 

[kmaxx98]

Thanks Ahoy!

Could you explain to me how that works?

See if this works for you:

	A	B	C	D	E	F	G	H
1	Date	Day	Value		Work Date		WTD Sum	MTD Sum
2	29-May	Tue	10		6-Jun		15	21
3	30-May	Wed	15
4	31-May	Thu	16
5	1-Jun	Fri	1
6	2-Jun	Sat	2
7	3-Jun	Sun	3
8	4-Jun	Mon	4
9	5-Jun	Tue	5
10	6-Jun	Wed	6
11	7-Jun	Thu	7
12	8-Jun	Fri	8
13	9-Jun	Sat	9
14	10-Jun	Sun	10
15	11-Jun	Mon	11
16	12-Jun	Tue	12

<COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY>
</TBODY>

Spreadsheet Formulas

Cell Formula G2 =SUMIFS(C2:C16,A2:A16,">="&DATE(YEAR(E2),MONTH(E2),DAY(E2)-WEEKDAY(E2,2)+1),A2:A16,"<="&E2) H2 =SUMIFS(C2:C16,A2:A16,">="&EOMONTH(E2,-1)+1,A2:A16,"<="&E2) <TBODY> </TBODY>

<TBODY>
</TBODY>

Excel tables to the web >> Excel Jeanie HTML 4

 

[AhoyNC]

For the WTD

The first criteria in the SUMIFS finds the Monday of the week.
DATE(YEAR(E2),MONTH(E2),DAY(E2)-WEEKDAY(E2,2)+1)

I used the DATE function to find the year & Month you wanted.
The DAY function returns 6 in this example (which is Wed.)

The Weekday function with a return value of 2 assigns a number to the weekdays in this case the 2 assigns 1=Monday thru 7=Sunday.
So in this case the WEEKDAY function assigns 3 to Wed and subtracts it from 6 which gives you 6/3/12 which is Sunday and the +1 just adds 1 to the date which will give you the Monday that you wanted to start on.

Criteria 2 in the SUMIFS just tells the function to add all values up to and equal to the date.

For the MTD :

Criteria 1 finds the first day of the month.<o></o>
I used the end of month function<o></o>
<o> </o>
EOMONTH(E2,-1)+1<o></o>
<o> </o>
The -1 finds the last day of the previous month 5/31/12 in this case.<o></o>
+1 adds 1 day to give 6/1/12.<o></o>
<o> </o>
Hope this helps.<o></o>