Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trying to get a Boolean formula to work month-to-month | 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 |