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. 


Thread Tools  Display Modes 
#1




Beginning of the month formula
Looking for the formula (non array) for the start of a month selected.
ie: if i select 31Jan 07 in cell A1. Cell A2 will show 01Jan07 and so on for the rest of the year?  Geo 
Ads 
#2




Beginning of the month formula
If A1 has 31Jan07 then this formula in A2 will show 1Jan07 (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 31Jan 07 in cell A1. > Cell A2 will show 01Jan07 > and so on for the rest of the year? >  > Geo 
#3




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 31Jan07 then this formula in A2 will show 1Jan07 (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 31Jan 07 in cell A1. > > Cell A2 will show 01Jan07 > > and so on for the rest of the year? > >  > > Geo > > > 
#4




Beginning of the month formula
With your start date in A1 try:
=A1DAY(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+33DAY(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 31Jan 07 in cell A1. > Cell A2 will show 01Jan07 > and so on for the rest of the year? >  > Geo > 
#5




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 1Jan2007 when the 'dropdown' cell has JAN? You originally said it has 31Jan2007 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 31Jan07 then this formula in A2 will show 1Jan07 (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 31Jan 07 in cell A1. >> > Cell A2 will show 01Jan07 >> > and so on for the rest of the year? >> >  >> > Geo >> >> >> 
#6




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 addin installed: =EOMONTH(A1,0) If you don't have the Analysis Toolpak addin installed: =A1+32DAY(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 31Jan07 then this formula in A2 will show 1Jan07 (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 31Jan 07 in cell A1. >> > Cell A2 will show 01Jan07 >> > and so on for the rest of the year? >> >  >> > Geo >> >> >> 
#7




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 addin installed: > > =EOMONTH(A1,0) > > If you don't have the Analysis Toolpak addin installed: > > =A1+32DAY(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 31Jan07 then this formula in A2 will show 1Jan07 (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 31Jan 07 in cell A1. > >> > Cell A2 will show 01Jan07 > >> > and so on for the rest of the year? > >> >  > >> > Geo > >> > >> > >> > > > 
#8




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 addin installed: > > > =EOMONTH(A1,0) > > > If you don't have the Analysis Toolpak addin installed: > > > =A1+32DAY(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 31Jan07 then this formula in A2 will show 1Jan07 (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 31Jan 07 in cell A1. > > >> > Cell A2 will show 01Jan07 > > >> > and so on for the rest of the year? > > >> >  > > >> > Geo Hide quoted text  > >  Show quoted text  
#9




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 addin installed: >> >> =EOMONTH(A1,0) >> >> If you don't have the Analysis Toolpak addin installed: >> >> =A1+32DAY(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 31Jan07 then this formula in A2 will show 1Jan07 (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 31Jan 07 in cell A1. >> >> > Cell A2 will show 01Jan07 >> >> > and so on for the rest of the year? >> >> >  >> >> > Geo >> >> >> >> >> >> >> >> >> 
Thread Tools  
Display Modes  


Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
Trying to get a Boolean formula to work monthtomonth  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 