SOLVED

Formula Help

Copper Contributor

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.

Can someone please help?

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. 

 

10 Replies

@CSchaeffer 

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

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

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

This has been my frustration.

@CSchaeffer 

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?

@OliverScheurich 

 

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

 

@CSchaeffer 

As variant

=IF( COUNTBLANK(A8), 15, IF( DAY(A8)<16,15,DAY(EOMONTH(A8,0))) )
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 (Copper Contributor)
Solution

@CSchaeffer 

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))) )
THAT worked! Thank you so much!

@CSchaeffer , you are welcome

1 best response

Accepted Solutions
best response confirmed by CSchaeffer (Copper Contributor)
Solution

@CSchaeffer 

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))) )

View solution in original post