Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old October 21st 07, 05:01 PM posted to microsoft.public.excel.misc
Geo Geo is offline
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 66
Default Beginning of the month formula

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

  #2   Report Post  
Old October 21st 07, 05:12 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 4,393
Default Beginning of the month formula

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



  #3   Report Post  
Old October 21st 07, 05:32 PM posted to microsoft.public.excel.misc
Geo Geo is offline
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 66
Default Beginning of the month formula

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




  #4   Report Post  
Old October 21st 07, 05:38 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 2,345
Default Beginning of the month formula

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


Replace @mailinator.com with @tiscali.co.uk


"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



  #5   Report Post  
Old October 21st 07, 05:45 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 4,393
Default Beginning of the month formula

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.
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"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








  #6   Report Post  
Old October 21st 07, 06:43 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 15,768
Default Beginning of the month formula

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






  #7   Report Post  
Old October 21st 07, 10:06 PM posted to microsoft.public.excel.misc
Geo Geo is offline
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 66
Default Beginning of the month formula

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






  #8   Report Post  
Old October 22nd 07, 01:26 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2007
Posts: 223
Default Beginning of the month formula

With ATP, or in Excel 2007:

=EOMONTH(A1,-1)+1


On Oct 21, 5:06 pm, Geo wrote:
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- Hide quoted text -


- Show quoted text -



  #9   Report Post  
Old October 22nd 07, 05:19 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 15,768
Default 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










Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Trying to get a Boolean formula to work month-to-month mstieler Excel Discussion (Misc queries) 4 July 18th 07 10:12 PM
Function or formula to convert "text" month to number of month? Steve Vincent Excel Discussion (Misc queries) 5 May 15th 07 01:11 AM
Function or formula to convert "text" month to number of month? Kevin Vaughn Excel Discussion (Misc queries) 0 February 4th 06 04:45 PM
Default to beginning of month Heather Excel Worksheet Functions 4 April 22nd 05 09:43 PM
How do I remove a blanck space from the beginning of a formula? kan2953 Excel Worksheet Functions 6 April 6th 05 05:38 PM


All times are GMT +1. The time now is 01:42 AM.

Powered by vBulletin® Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017