Getting the for day and last day of the month

[Learning123]

Hi everyone!
I was wondering if there is a code to display the first day of the month (example: 12/01/2002) and also is it possible to display the last day of the month (example 12/31/2002)? Any ideas and suggestions are gratefully welcome =) . As always Thank you for you kind and helpful words!

ndy

 

[xlbo]

Well, for the 1st day of the current month, use:
=MONTH(NOW())&"/01/"&YEAR(NOW())
(for mm/dd/yy format)
And for the last day, use:
=EOMONTH(VALUE(NOW()),0)
I think you will need to have the Analysis Toolpak installed for the EOMONTH function tho

HTH
~Geoff~

 

[iGrant]

You can use the DATE function to create a date from its constituent parts. So,

=DATE(2002,12,01)

will return the 1st December 2002. The neat bit is that you can combine functions within the arguments. If you also know that you can extract the year, month, & day values from a date, you could use:

=DATE(YEAR(A1),MONTH(A1),1)

to return the 1st day of whatever month & year are included in a date entered in cell A1. This allows you to do some neat things like, for example, add 2 months to a date with:

=DATE(YEAR(A1),MONTH(A1)+2,DAY(A1))

Hope this helps

 

[Learning123]

Thanks a lot guys! really helped!!