Sum every 7 days

[prudential]

Hi, can someone pls help. I need it to calculate the weekly sum in the C column.

i.e. every 7 days there should be a number in the C column showing the profit/loss for that week.

thanks in advance.

[JBeaucaire]

Use a PIVOT TABLE.

1) First you need to add a column to your table to extract just the date.
C1: Date
C2: =INT(A2)
Copy C2 down and format as short date.

2) Highlight columns A:C and choose INSERT > PIVOT TABLE > Existing Worksheet (click on G1) > OK

3) Put the DATE field in as Row Labels
Put the Trade P/L field as Values and use the FIELD SETTINGS to change it to SUM of Trade P/L

4) Right-Click on any date in column G of the pivot table and select GROUP> By DAYS > Number of days: 7 > OK
You can set the starting date specifically to the date you want the "first day of first week", if you wish.


Done. Should look like this:
https://www.screencast.com/t/hJLqh7VKg

[JohnTopley]

The dates are mm/dd/yy hh:mm? and some fields are formatted as GENERAL so Your data needs tidying up.

[prudential]

Thanks for the assistance.

Tried int(A2) and got mostly #VALUE! errors.

BTW, this is a CSV download. I wouldnt create this garbage

[JBeaucaire]

I didn't get any errors on the data you uploaded. Maybe:

=INT(TRIM(A2))

[prudential]

I didn't get any errors on the data you uploaded. Maybe:

=INT(TRIM(A2))

Thanks for your assistance.

OK, that seems to work for all the cells that have "AM" to the right of the cell but not for the others. How about we try to add "AM" to all cells that dont have it. Would CONCATENATE do this?

[JBeaucaire]

No, the AM PM in time cells are simply a display "trick". Unless your real data is not what it appears to be. You would need to show me the actual problem workbook.

[prudential]

File added with both forumulas applied to colum c & d

Please review.

[JBeaucaire]

As noted before, the AM/PM thing isn't usually real, just a formatting trick of Excel. Both C & D look correct to my eye:
https://www.screencast.com/t/GuGwoc8C

[prudential]

Thanks for your assistance.

Put simply, what do I need to do to get the same result as you? My formulas are exactly the same but its showing up different (ie. with #VALUE! errors)

[prudential]

Clearly you're playing games because I just formatted the A column in 5 seconds using Find and Replace.

Thanks for your help.

[JBeaucaire]

No, I'm not playing games. Interesting comment that one...

Anyway, I am glad you found a workaround for a problem you saw on my videos I was not having with your workbook. Probably some regional thing...

So, the Pivots are working for you now?

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[prudential]

The group by days function isnt available. If I select one cell it says "group not available" when I select multiple cells it groups those cells but no group by days option comes up.

[JBeaucaire]

ok, then something is definitely up with your system. The GROUP function works numeric cells and as you say in my video using your workbook, my system has detected your dates as actual dates which are secretly numeric in the background, but on your system I'm going to guess your system things those cells are text. It looks like dates to your eye, but in the background its not.

I'm guessing. Usually the problems people have in their workbooks we can see, too. Hmm, what to do?

Are you up for a quick Teamviewer session? That would let me peek over your shoulder directly on your computer, maybe I can suss it out.

Send me a Private Message or reach out to me on Skype and can talk directly, see what we see.
Skype: Jerry Beaucaire (devtuxx7069)

[prudential]

Its OK, I changed my system and excel locale and got it working. Plus cleaned up the dates a bit more.

BTW, this doesnt tell me the weekly profit, all it does is group the profit into 7 day blocks. There's weeks where there is no income but it doesnt show this.

I'll just work out the weekly income for the period which should be good enough.

Thanks.

[JBeaucaire]

At the point of creation of the Pivot table, you can manually set the START date to insure the 7-day blocks are in the correct weekly groupings as well. The Pivot tools have so many variations...