Formula seems to be referencing the formula and not the value of referenced cell

[Lance1227]

I am trying to set up a yearly payroll file with 12 sheets for each pay period. Each sheet will start on the 21st and end on the 20th of the following month. I am trying to have each sheet auto populate with the dates and days of the week. I have custom formatted the fields as “dd”. A simple =A1+1, will work for most of the month, but I need it to stop when it reaches the 20th. If the previous month has less than 31 days then the last cell(s) in the row should be blank. I tried =IF(BA1<20,BA1+1,""). I also tried referencing a different cell with the date in it =IF(BA1<BX3, BA1+1). It seems that my formulas might be picking up the formula and not cell value. I tried several options to have my formula only use the value of the cell, but so far I haven’t found a solution. Any assistance would be greatly appreciated.

 

[AlphaFrog]

Your cells have full dates in them. You are only displaying the Day value of the date because of cell formatting, but that displayed formatting doesn't change the cell date value.

Try something like this...

=IF(DAY(BA1)<20,BA1+1,"")

 

[Lance1227]

I really appreciate your quick response. I had previously tried that. I works for the first cell after it reached "20", but subsequent cells have an #VALUE error

 

[AlphaFrog]

=IF(BA1="", "", IF(DAY(BA1)<20,BA1+1,""))

 

[Lance1227]

It seems to work! I can't tell you how many hours I spent on this. You are an Excel god amongst us mere mortals. I really appreciate your assistance!

 

[AlphaFrog]

You're welcome, and thank you for the feedback.