1st day of month to 'Week Commencing' issue

[benicholson100]

Hi guys,

I am trying to populate cash outflows based on payment date of month (ie 1st, 10th in column G) to weeks (ie week commencing 27th April - always a Monday - in Row 7).

The below image shows my formula works for everything but 1st of the month where the week commencing crosses over a month (e.g. 27th April and 27th July but populates 1st June).

Can anyone help find a way around this?

Many thanks
Ben

 

[GraH]

Hi Ben,
Would this work [E150]=IF(ISERROR(MATCH($G150,DAY(I$7+{0,1,2,3,4,5,6}),0)),"",$F150)?

 

[jasonb75]

A much simpler formula for you, this should work with any day in column G

=IF(OR(DAY(I$7+{0,1,2,3,4,5,6})=$G150),$F150,"")

You will still have missing entries if the day in column G is greater than the last day of a specific month (i.e. payment day of 30 would be missing in Feb).

 

[benicholson100]

Ah, yes. That is much simper and works.

Thank you both