Show Cell Value Only if Date is between two Dates

[jski21]

Good afternoon Mr. Excel Team,

Trying to have a budget line item show up under the appropriate month only if that month is less than the current date and if true, calculate the monthly budget amount. Using this formula: =IF(B8<$A$3,$N$10/12,""). Right now the formula should show only the months that are prior to today (October 2021), yes? However, carrying the formula across the range, all the cells populate with the calculated monthly budgeted amount. BTW, using a fiscal year format for what it's worth.

B8 = Date of the appropriate month
$A$3 = Today's date
$N$10/12 = the annual budget amount divided into monthly increments

Here's the spreadsheet:

Operating Budget2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
3	As of October 2021
4
5
6		Budget	Budget	Budget	Budget	Budget	Budget	Budget	Budget	Budget	Budget	Budget	Budget	Budget
7														Total
8	ACCOUNT NAME	06/30/21	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Annual
9	General Administration
10	Monthly Expense	361,970	361,970	361,970	361,970	361,970	361,970	361,970	361,970	361,970	361,970	361,970	361,970	4,343,636
SalCap (2)

Cell Formulas
Range	Formula
A3	A3	="As of "&TEXT(TODAY(),"mmmm yyyy")
B8	B8	=EOMONTH($C$2,5)
C8	C8	=EOMONTH($C$2,6)
D8	D8	=EOMONTH($C$2,7)
E8	E8	=EOMONTH($C$2,8)
F8	F8	=EOMONTH($C$2,9)
G8	G8	=EOMONTH($C$2,10)
H8	H8	=EOMONTH($C$2,11)
I8	I8	=EOMONTH($C$2,12)
J8	J8	=EOMONTH($C$2,13)
K8	K8	=EOMONTH($C$2,14)
L8	L8	=EOMONTH($C$2,15)
M8	M8	=EOMONTH($C$2,16)
B10:M10	B10	=IF(B8<$A$3,$N$10/12,"")
N10	N10	=21718180*0.2

Thanks in advance for the look/see and assistance.


jski

 

[jasonb75]

A3 doesn't contain a date, it contains a text string that includes a date. To compare a date to A3 you would need to change it so that it holds a date only. Either that, or use TODAY() in your formulas instead of referring to A3.

 

[jski21]

Used the latter method. Occam's razor strikes again. Thanks Well-known Member Jasonb75!​

​

 

[jasonb75]

Occam's razor strikes again.

That is often the case where excel is involved.

Happy to help and thanks for the feedback!