Google sheets help - If formula with date issue.

[dantray]

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?

 

[jasonb75]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M
1				1	8	15	22	29	6	13	20	27	3
2				01/11/2021	08/11/2021	15/11/2021	22/11/2021	29/11/2021	06/12/2021	13/12/2021	20/12/2021	27/12/2021	03/01/2022
3	29		5000					5000				5000
Sheet1

Cell Formulas
Range	Formula
D1:M1	D1	=DAY(D2)
D3:M3	D3	=IF($A3="w",$C3,IF(OR(DAY(D$2+{0,1,2,3,4,5,6})=$A3),$C3,""))

 

[dantray]

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.

 

[jasonb75]

Try this instead

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