SOLVED

Formula Help

%3CLINGO-SUB%20id%3D%22lingo-sub-3285800%22%20slang%3D%22en-US%22%3EFormula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3285800%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20writing%20what%20I%20feel%20is%20a%20bit%20of%20a%20complicated%20formula%20where%20one%20cell%20points%20to%20another%2C%20and%20then%20that%20one%20is%20looked%20at%20by%20another%20and%20so%20on.%20I%20am%20stuck%20with%20one%20area.%20I%20have%20a%20cell%20that%20looks%20at%20the%20date.%20If%20the%20date%20is%20greater%20than%20the%2015th%20of%20the%20month%20it%20returns%20a%20number%20which%20shows%20a%20value%20in%20yet%20another%20cell.%2015%20or%20less%20return%20a%20different%20number%20that%20relates%20to%20a%20different%20value.%20My%20issue%20is%20adding%20a%20formula%20to%20show%20a%20number%20if%20there%20is%20no%20date%20at%20all.%20Basically%2C%20I%20am%20looking%20to%20add%20an%20ISBLANK%20to%20the%20formula%20but%20it%20keeps%20giving%20me%20an%20error%20no%20matter%20how%20I%20do%20this.%3C%2FP%3E%3CP%3ECan%20someone%20please%20help%3F%3C%2FP%3E%3CP%3EHere%20is%20what%20I%20am%20looking%20at%20combining.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(DAY(A8)%26lt%3B16%2C15%2CDAY(EOMONTH(A8%2C0)))%3C%2FP%3E%3CP%3E%3DIF(ISBLANK(A8)%2C%22%22%2CA8))%20In%20this%20string%20I%20would%20like%20the%20number%20value%20to%20be%2015%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20I%20somehow%20combine%20these%20to%20show%20if%20the%20day%20is%20blank%20return%20a%2015%20and%20if%20its%20the%201st%20through%20the%2015th%20it%20returns%20a%2015.%20Anything%20over%20that%20returns%20end%20of%20month.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20that%20makes%20sense.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3285800%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3286171%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3286171%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1363640%22%20target%3D%22_blank%22%3E%40CSchaeffer%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3286093%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3286093%22%20slang%3D%22en-US%22%3ETHAT%20worked!%20Thank%20you%20so%20much!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3286051%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3286051%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1363640%22%20target%3D%22_blank%22%3E%40CSchaeffer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESo%2C%20you%20don't%20have%20blank%20cell.%20You%20have%20cell%20with%20some%20text%2C%20e.g.%20with%20space.%20That%20could%20work%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DIF(%20ISTEXT(A8)%2C%2015%2C%20IF(%20DAY(A8)%26lt%3B16%2C15%2CDAY(EOMONTH(A8%2C0)))%20)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3285917%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3285917%22%20slang%3D%22en-US%22%3EI%20am%20getting%20the%20same%20result.%20It%20all%20seems%20to%20be%20working%20but%20as%20soon%20as%20I%20hit%20the%20spacebar%20on%20the%20date%20and%20tab%20out%20the%20cell%20shows%20%23VALUE!%3CBR%20%2F%3EAnd%20that's%20been%20my%20headache%20for%203%20days%20now.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3285904%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3285904%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1363640%22%20target%3D%22_blank%22%3E%40CSchaeffer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DIF(%20COUNTBLANK(A8)%2C%2015%2C%20IF(%20DAY(A8)%26lt%3B16%2C15%2CDAY(EOMONTH(A8%2C0)))%20)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3285898%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3285898%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1174419%22%20target%3D%22_blank%22%3E%40Quadruple_Pawn%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20dates%20that%20were%20blank%20looked%20to%20be%20okay%20until%20I%20entered%20a%20date%2C%20and%20then%20blanked%20it%20later.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3285839%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3285839%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1363640%22%20target%3D%22_blank%22%3E%40CSchaeffer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThat's%20surprising.%20As%20shown%20in%20the%20pdf.%20file%20it%20returns%2015%20if%20cell%20A8%20is%20blank.%20Does%20it%20work%20when%20you%20open%20the%20attached%20spreadsheet%3F%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3285815%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3285815%22%20slang%3D%22en-US%22%3EIt%20works%20until%20I%20blank%20the%20date%20in%20cell%20A8.%20Then%20it%20returns%20%23VALUE!%3CBR%20%2F%3E%3CBR%20%2F%3EThis%20has%20been%20my%20frustration.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3285807%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3285807%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1363640%22%20target%3D%22_blank%22%3E%40CSchaeffer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DIF(OR(ISBLANK(A8)%2CDAY(A8)%26lt%3B16)%2C15%2CDAY(EOMONTH(A8%2C0)))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EYou%20can%20try%20this%20formula%20which%20seems%20to%20work%20in%20my%20sheet.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional 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. 

 

9 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?

@Quadruple_Pawn 

 

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 (Occasional 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