 SOLVED

# Formula Help

I am writing what I feel is a bit of a complicated formula where one cell points to another, and then that one is looked at by another and so on. I am stuck with one area. I have a cell that looks at the date. If the date is greater than the 15th of the month it returns a number which shows a value in yet another cell. 15 or less return a different number that relates to a different value. My issue is adding a formula to show a number if there is no date at all. Basically, I am looking to add an ISBLANK to the formula but it keeps giving me an error no matter how I do this.

Here is what I am looking at combining.

=IF(DAY(A8)<16,15,DAY(EOMONTH(A8,0)))

=IF(ISBLANK(A8),"",A8)) In this string I would like the number value to be 15

Can I somehow combine these to show if the day is blank return a 15 and if its the 1st through the 15th it returns a 15. Anything over that returns end of month.

I hope that makes sense.

9 Replies

# Re: Formula Help

``=IF(OR(ISBLANK(A8),DAY(A8)<16),15,DAY(EOMONTH(A8,0)))``

You can try this formula which seems to work in my sheet.

# Re: Formula Help

It works until I blank the date in cell A8. Then it returns #VALUE!

This has been my frustration.

# Re: Formula Help

That's surprising. As shown in the pdf. file it returns 15 if cell A8 is blank. Does it work when you open the attached spreadsheet?

# Re: Formula Help

The dates that were blank looked to be okay until I entered a date, and then blanked it later.

# Re: Formula Help

As variant

``=IF( COUNTBLANK(A8), 15, IF( DAY(A8)<16,15,DAY(EOMONTH(A8,0))) )``

# Re: Formula Help

I am getting the same result. It all seems to be working but as soon as I hit the spacebar on the date and tab out the cell shows #VALUE!
And that's been my headache for 3 days now.
best response confirmed by CSchaeffer (Occasional Contributor)
Solution

# Re: Formula Help

So, you don't have blank cell. You have cell with some text, e.g. with space. That could work

``=IF( ISTEXT(A8), 15, IF( DAY(A8)<16,15,DAY(EOMONTH(A8,0))) )``

# Re: Formula Help

THAT worked! Thank you so much!

# Re: Formula Help

@CSchaeffer , you are welcome