Posted to microsoft.public.excel.misc
|
|
Beginning of the month formula
You're welcome. Thanks for the feedback!
--
Biff
Microsoft Excel MVP
"Geo" wrote in message
...
Thank you that works fine. Great stuff.
--
Geo
"T. Valko" wrote:
If your drop down list contains the month names as TEXT entries: January,
February, March, April, etc... (they can also be the short month names:
Jan,
Feb, Mar, etc)
Assume the drop down is in cell C1.
For the 1st of the month (for the CURRENT year) in cell A1:
=("1 "&C1)+0
Format as DATE
For the end of the month (for the CURRENT year) in cell A2:
If you have the Analysis Toolpak add-in installed:
=EOMONTH(A1,0)
If you don't have the Analysis Toolpak add-in installed:
=A1+32-DAY(A1+32)
Format as DATE
--
Biff
Microsoft Excel MVP
"Geo" wrote in message
...
Thanks bernard,
I have a validation drop down list that when I select January then on
cells
A1 will show Beginning of that month and A2 the end of that month. The
drop
down list has Jan to Dec. I just need to sort the cells out to show the
dates
of the present year.
--
Geo
"Bernard Liengme" wrote:
If A1 has 31-Jan-07 then this formula in A2 will show 1-Jan-07 (no
need
to
'select A1): =DATE(YEAR(A1),MONTH(A1),1). You may need to format the
cell
as
it might display the date as 01/01/2007
You want something "For the rest of the year"? Not clear what this
means.
If
B1 has a Feb date then the same formula copied to B2 will work.
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email
"Geo" wrote in message
...
Looking for the formula (non array) for the start of a month
selected.
ie: if i select 31-Jan 07 in cell A1.
Cell A2 will show 01-Jan-07
and so on for the rest of the year?
--
Geo
|