A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Beginning of the month formula



 
 
Thread Tools Display Modes
  #1  
Old October 21st 07, 05:01 PM posted to microsoft.public.excel.misc
Geo
external usenet poster
 
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
Ads
  #2  
Old October 21st 07, 05:12 PM posted to microsoft.public.excel.misc
Bernard Liengme
external usenet poster
 
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  
Old October 21st 07, 05:32 PM posted to microsoft.public.excel.misc
Geo
external usenet poster
 
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  
Old October 21st 07, 05:38 PM posted to microsoft.public.excel.misc
Sandy Mann
external usenet poster
 
Posts: 2,348
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  
Old October 21st 07, 05:45 PM posted to microsoft.public.excel.misc
Bernard Liengme
external usenet poster
 
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  
Old October 21st 07, 06:43 PM posted to microsoft.public.excel.misc
T. Valko
external usenet poster
 
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  
Old October 21st 07, 10:06 PM posted to microsoft.public.excel.misc
Geo
external usenet poster
 
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  
Old October 22nd 07, 01:26 AM posted to microsoft.public.excel.misc
iliace
external usenet poster
 
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  
Old October 22nd 07, 05:19 AM posted to microsoft.public.excel.misc
T. Valko
external usenet poster
 
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
>> >>
>> >>
>> >>

>>
>>
>>



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

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 09:18 PM.


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