• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

inddon

Member
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
 
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!
 
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,
 
Back
Top