Cell value that changes depending on the current date.

olimajor123

Board Regular
Joined
Nov 13, 2013
Messages
72
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
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

gaz_chops

Well-known Member
Joined
Apr 29, 2003
Messages
6,058
Platform
MacOS
Try

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

A1 = the date
 

Watch MrExcel Video

Forum statistics

Threads
1,095,925
Messages
5,447,303
Members
405,447
Latest member
WPY

This Week's Hot Topics

Top