Home 
Search 
Today's Posts 
#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 
#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 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Trying to get a Boolean formula to work monthtomonth  Excel Discussion (Misc queries)  
Function or formula to convert "text" month to number of month?  Excel Discussion (Misc queries)  
Function or formula to convert "text" month to number of month?  Excel Discussion (Misc queries)  
Default to beginning of month  Excel Worksheet Functions  
How do I remove a blanck space from the beginning of a formula?  Excel Worksheet Functions 