To check a given date is during, 1st & last day of month in Excel 2003

[inddon]

Hello There,

I have a given date, and I would like to know whether this date:
1. Is any date during the month (Output: During the Month)
2. Is the 1st and the last day of the month. (Output: Not during the Month)

The formula needs to be build compatible to Excel version 2003

Example:

01-Jan-2018 Not During the Month
02-Jan-2018 During the Month
28-Feb-2018 Not During the Month
31-Mar-2018 Not During the Month
15-Apr-2018 During the Month

Look forward to hearing from you.

Regards,
Don

 

[bosco_yip]

Maybe,



B1, copied down:

=IF(OR(A1=EOMONTH(A1,-1)+1,A1=EOMONTH(A1,0)),"Not During the Month","During the Month")

Regards
Bosco

 

[inddon]

Thank you Bosco

 

[Peter Bartholomew]

Very similar to Bosco's solution but stopping at the point where [0,1] is returned.
= (DAY(date)=1) + (date=EOMONTH(+date,0))
Then a custom number format
"Not during month"; ;"During the Month"
is applied to convey the required message.

 

[Khalid NGO]

Hi to all,
Peter's solution, but with TEXT function:

=TEXT((DAY(A1)=1)+(A1=EOMONTH(A1,0)),"""Not during Month"";;""During Month""")

Regards,

 

[Peter Bartholomew]

The same solution, yes but I didn't even recognise it at first!
I played with it further to introduce a three-way switch
= TEXT( (DAY(A1)=1)-(A1=EOMONTH(A1,0)), numberFormat)
where 'numberFormat' refers to
="""Start of Month"";""End of Month"";""During Month"""
Thanks Khalid; now all I have to do is remember to use it!

 

[Khalid NGO]

Hi Peter,
Great…

Yes, formatting with TEXT function is bit tricky to me as compared to Cell Formatting, I often forget the awful """ 3 inverted commas implementation.

Regards,