Cell value that changes depending on the current date.

[olimajor123]

Hi there,

I am preparing a budgeting document and for one of the items I require the value in a cell to change depending on what day of the month it is:

For example, if the budget for something is £3500, I want cell J13 to show:

3500/4 if the current date is the 1st-7th of the month.
3500/4*2 if the current date is the 8th-14th of the month.
3500/4*3 if the current date is the 15th-21st of the month.
3500/4*4 if the current date is the 22nd-end of the month.

Does anyone know a formula for this or alternatively, if there is a simplier way to do this. I will need it for each month so there could be issues in repeating a formula for months with different amounts of days.

Any help would be appreciated.

Thanks

 

[gaz_chops]

Try

=3500/4*LOOKUP(DAY(A1),{0,8,15,22},{1,2,3,4})

A1 = the date

 

[olimajor123]

This is great. Thanks

 

[gaz_chops]

You're welcome.