Beginning of the month formula

[Geo]

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?

 

[Bernard Liengme]

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

 

[Geo]

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.

 

[Sandy Mann]

With your start date in A1 try:

=A1-DAY(A1)+1

If by:

and so on for the rest of the year?

you mean you want each 1st of the month from then on then try:

=B1+33-DAY(B1+33)+1

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Bernard Liengme]

Try that again.
In some cell you can get any of: "JAN", "FEB" ......
Are these text or dates that just display the month?
Does A1 automatically display 1-Jan-2007 when the 'drop-down' cell has JAN?
You originally said it has 31-Jan-2007
I want to help so please keep at it.

 

[T. Valko]

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

 

[Geo]

Thank you that works fine. Great stuff.
--
Geo

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

 

[iliace]

With ATP, or in Excel 2007:

=EOMONTH(A1,-1)+1

Thank you that works fine. Great stuff.
--
Geo











- Show quoted text -

 

[T. Valko]

You're welcome. Thanks for the feedback!