Google sheets help - If formula with date issue.

dantray

New Member
Joined
Jan 7, 2014
Messages
5
Couldn't find a forum on this site for google sheets, so posting in here instead.

I am trying to create a cashflow forecast, using an if formula to test what the day of the month is, and use this to allocate a particular cost/revenue to a column relative to that week.

Basically:
If (day of expected cost) is greater or equal to the start day of the current week, but less than the start day of the next week, allocate cost to current week, or leave blank.

This works fine throughout the month, but at month end I have an issue as the start day of the next week, say 6th (6) is less than the start of this week, say 29th (29). Causing the formula to return nothing.

Best described with an example: Month end date issue

Can anyone tell me the correct formula to use to make this work please?
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Couldn't find a forum on this site for google sheets,
There is one for 'other applications' which includes google sheets. I was going to ask a moderator to move it for you but in all honesty, I expect that for what you're asking the solution will be the same for excel and google so it doesn't really need a dedicated sheets answer. Your sheet is not editable so I've mocked up a quick test in excel. Note that it uses the actual dates in row 2 for testing, not the day numbers in row 1. My main concern is that you've used the LEFT function to extract the day from the date, which in excel would tell me that they are not valid dates although it appears to be slightly different in sheets.

Book1
ABCDEFGHIJKLM
1 1815222961320273
201/11/202108/11/202115/11/202122/11/202129/11/202106/12/202113/12/202120/12/202127/12/202103/01/2022
3295000    5000   5000 
Sheet1
Cell Formulas
RangeFormula
D1:M1D1=DAY(D2)
D3:M3D3=IF($A3="w",$C3,IF(OR(DAY(D$2+{0,1,2,3,4,5,6})=$A3),$C3,""))
 
Upvote 0
Thanks, although I tested and doesn't seem to work, I have changed permissions now to edit, would you mind putting it in that one please?
Thanks again.
 
Upvote 0
Try this instead

Excel Formula:
=ArrayFormula(IF($A3="w",$C3,IF(OR(DAY(D$2+{0,1,2,3,4,5,6})=$A3),$C3,"")))
 
Upvote 0

Forum statistics

Threads
1,223,350
Messages
6,171,590
Members
452,412
Latest member
sprichwort

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?

Disable AdBlock

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
Back
Top