Week to Date excel formula

[cartica]

Hi-

I am currently using this formula to pull the number of days week to date.

Please Login or Register  to view this content.

. This returns the correct # 2. Monday/Tuesday. My question is, what formula would I need to pull in data for total USD week to date? Mind you, I do not want to have to change this daily. So for tomorrow, it should pull in the data for Monday, Tuesday, Wed...etc. Just using a sumproduct for cells B2:B2000, will not work as my data begins on 8/1/13.

Below Columns

Trade Date USD Amount
8/12/2013 6,666,769.98
8/12/2013 2,999,280.59
8/12/2013 609,716.58
8/12/2013 7,074,640.90
8/13/2013 1,282,990.31
8/13/2013 1,007,505.45
8/13/2013 515,396.08
8/13/2013 565,550.05

[Ace_XL]

Try

=SUMPRODUCT(($A$2:$A$2000>=TODAY()-WEEKDAY(TODAY()-1)+1)*($A$2:$A$2000<TODAY()-WEEKDAY(TODAY()-1)+8),$B$2:$B$2000)

[cartica]

Try

=SUMPRODUCT(($A$2:$A$2000>=TODAY()-WEEKDAY(TODAY()-1)+1)*($A$2:$A$2000<TODAY()-WEEKDAY(TODAY()-1)+8),$B$2:$B$2000)

I was unable to get your formula work. Can you try inputting it in my attached spreadshett in teh Trading Statistics tab. The data I want pulled from is in the eze data tab. I have highlighted the 2 columns in yellow. ThanksMiddle Office Report 1.xlsx

[Ace_XL]

The formula is

=SUMPRODUCT(('EZE DATA'!$B$2:$B$60>=TODAY()-WEEKDAY(TODAY()-1)+1)*('EZE DATA'!$B$2:$B$60<TODAY()-WEEKDAY(TODAY()-1)+8),'EZE DATA'!$Q$2:$Q$60)

Your data in column B is being treated as text, hence the anomaly. Do this..
- In a separate cell input 1
- Copy this cell
- Select your rnage of data (column B)
- Paste special -- values -- multiply
- Format as date

The formula should work then

[cartica]

Thanks. That worked. I am kind of confused on what the below part of forumla is saying. Not sure if there is a simpler formula.

($A$2:$A$2000>=TODAY()-WEEKDAY(TODAY()-1)+1)*($A$2:$A$2000<TODAY()-WEEKDAY(TODAY()-1)+8)

[daddylonglegs]

This part

TODAY()-WEEKDAY(TODAY()-1)+1

gives you the Sunday of this week and......

TODAY()-WEEKDAY(TODAY()-1)+8

....gives you the following Sunday so the formula sums all the data associated with dates >= the former and < the latter. If you want a simpler formula then try something like this:

Put the first of those formulas in a cell, e.g. in E2 put

=TODAY()-WEEKDAY(TODAY()-1)+1

then in F2 you can use this formula

=SUMIFS('EZE DATA'!$Q$2:$Q$60,'EZE DATA'!$B$2:$B$60,">="&E2,'EZE DATA'!$B$2:$B$60,"<"&E2+7)

[cartica]

how does this =sunday? Today is Wed. Isn't that 4? wouldnt this sum up to 0?

TODAY()-WEEKDAY(TODAY()-1)+1

[daddylonglegs]

Sorry, it gives you Monday not Sunday, so it sums from Monday to Sunday in the current week.

Try that formula in a cell

=TODAY()-WEEKDAY(TODAY()-1)+1

It will give Monday 12 August 2013 at the moment

TODAY()-1 is yesterday and so WEEKDAY(TODAY()-1) = 3, so you get

=TODAY()-3+1 = Monday

Tomorrow the weekday part will give you 4 but because you are a day further on in the week you still get the same Monday date. It only changes when you get to next Monday - on that date it returns 19 August 2013 for a week.

You can test it by changing TODAY() for a cell reference, e.g.

=A1-WEEKDAY(A1-1)+1

Now put any date in A1 and see what the formula returns - it should always be the Monday on or before the A1 date

[Note: I used that version because Ace did but you can also get Monday of the week with the slightly simpler =A1-WEEKDAY(A1,3) ]