Mid-Month or End-of-Month

[Tom]

Is there a function that will enable me to interpret the mid-month date
(15th) or end-of-month date?

In other words, if e.g. the date = "April 15, 2004", then show "true". If
however, the date is "April 30, 2004" then show "false".

Important though is that the end-of-month date can have 4 different values
(28th or 29th or 30th or 31st... depending on the given month).

Any thoughts?!

Thanks,
Tom

 

[Chip Pearson]

Tom,

Try something like the following formula, with the date in A1.

=IF(DAY(A1)=15,"mid-month",IF(DAY(A1)=DAY(DATE(YEAR(A1),MONTH(A1)
+1,0)),"end of month","false"))



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Peo Sjoblom]

One way

=IF(DAY(A1)=15,"Mid",IF(DAY(A1)=DAY(DATE(YEAR(A1),MONTH(A1)+1,0)),"End","Nei
ther mid nor end of the month"))

replace Mid and End with TRUE and FALSE (without quotation marks) if needed

 

[Tom]

Chip,

that works fantastically!!!

Thanks,
Tom

 

[Tom]

Peo:

Great... it works fabulously!!!

Tom

One way

=IF(DAY(A1)=15,"Mid",IF(DAY(A1)=DAY(DATE(YEAR(A1),MONTH(A1)+1,0)),"End","Nei
ther mid nor end of the month"))

replace Mid and End with TRUE and FALSE (without quotation marks) if needed

--

Regards,

Peo Sjoblom

 

[Ron Rosenfeld]

Is there a function that will enable me to interpret the mid-month date
(15th) or end-of-month date?

In other words, if e.g. the date = "April 15, 2004", then show "true". If
however, the date is "April 30, 2004" then show "false".

Important though is that the end-of-month date can have 4 different values
(28th or 29th or 30th or 31st... depending on the given month).

Any thoughts?!

Thanks,
Tom

To check for the end of month:

=DAY(A1+1)=1

To check for mid month:

=DAY(A1)=15

These formulas will return TRUE if END OF MONTH or MID-MONTH respectively.

So, depending on how you want to use the results:

According to what you wrote, you want to return TRUE if the date is April 15,
2004 but return FALSE if the date is April 30, 2004 (and END OF MONTH date).

You don't say what you want to return if the date is neither.

=IF(DAY(A1+1)=1,FALSE,IF(DAY(A1)=15,TRUE," undefined"))


--ron