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

#### kmaxx98

##### New Member
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 Input Output Date Day Value Work Date WTD Sum MTD Sum 29-May Tue 10 6-Jun ?? ?? 30-May Wed 15 31-May Thu 16 1-Jun Fri 1 2-Jun Sat 2 3-Jun Sun 3 4-Jun Mon 4 5-Jun Tue 5 6-Jun Wed 6 7-Jun Thu 7 8-Jun Fri 8 9-Jun Sat 9

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

### Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
See if this works for you:
Excel Workbook
ABCDEFGH
1DateDayValueWork DateWTD SumMTD Sum
229-MayTue106-Jun1521
330-MayWed15
431-MayThu16
51-JunFri1
62-JunSat2
73-JunSun3
84-JunMon4
95-JunTue5
106-JunWed6
117-JunThu7
128-JunFri8
139-JunSat9
1410-JunSun10
1511-JunMon11
1612-JunTue12
Sheet

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>

 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

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>

Replies
4
Views
157
Replies
3
Views
148
Replies
0
Views
937
Replies
4
Views
162
Replies
1
Views
86

1,207,261
Messages
6,077,356
Members
446,279
Latest member
hoangquan2310

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back