| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date fomula not working
I have use this last year, it's fine until now. the formula is now new to Ron,
=DATE(YEAR(A2)-1,MONTH(A2),DAY(A2))-(MONTH(DATE(YEAR(A2)-1,MONTH(A2),DAY(A2)))<MONTH(A2)) That is, I enter a month and day in A2 and it will appear as last year's date, but now nothing changes, still in default year 2009. Please advise. -- Regards FangYR Malaysia |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date fomula not working
.. I enter a month and day in A2
I'm not sure what your expression computes but A2 must contain a real date, not just any sort of number. A real date is a full valid, unambiguous date (day-month-year) recognized by Excel, eg: 01-Jan-2009. Try re-entering a real date into A2. -- Max Singapore http://savefile.com/projects/236895 Downloads:23,000 Files:370 Subscribers:66 xdemechanik --- "FangYR" wrote: I have use this last year, it's fine until now. the formula is now new to Ron, =DATE(YEAR(A2)-1,MONTH(A2),DAY(A2))-(MONTH(DATE(YEAR(A2)-1,MONTH(A2),DAY(A2)))<MONTH(A2)) That is, I enter a month and day in A2 and it will appear as last year's date, but now nothing changes, still in default year 2009. Please advise |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date fomula not working
Your formula works for me. I entered 3/1 (March 1st) in to A2, and got
2008-03-01 in my target cell. So your problem must be you are not entering the date correctly. Regards, Fred. "FangYR" wrote in message ... I have use this last year, it's fine until now. the formula is now new to Ron, =DATE(YEAR(A2)-1,MONTH(A2),DAY(A2))-(MONTH(DATE(YEAR(A2)-1,MONTH(A2),DAY(A2)))<MONTH(A2)) That is, I enter a month and day in A2 and it will appear as last year's date, but now nothing changes, still in default year 2009. Please advise. -- Regards FangYR Malaysia |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date fomula not working
Hi All,
I have checked "Add-ons", install all, yet same result. to be specific, cell G2, the formula, in A2, I enter "1/3" (ie 3th Jan) and I expect A2 to read "3 Jan 2008", but "3 Jan 2009" appeared. Driving me cracy. Any thing I might have miss out? -- Regards FangYR Malaysia "Fred Smith" wrote: Your formula works for me. I entered 3/1 (March 1st) in to A2, and got 2008-03-01 in my target cell. So your problem must be you are not entering the date correctly. Regards, Fred. "FangYR" wrote in message ... I have use this last year, it's fine until now. the formula is now new to Ron, =DATE(YEAR(A2)-1,MONTH(A2),DAY(A2))-(MONTH(DATE(YEAR(A2)-1,MONTH(A2),DAY(A2)))<MONTH(A2)) That is, I enter a month and day in A2 and it will appear as last year's date, but now nothing changes, still in default year 2009. Please advise. -- Regards FangYR Malaysia |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date fomula not working
On Sat, 14 Feb 2009 21:46:01 -0800, FangYR
wrote: Hi All, I have checked "Add-ons", install all, yet same result. to be specific, cell G2, the formula, in A2, I enter "1/3" (ie 3th Jan) and I expect A2 to read "3 Jan 2008", but "3 Jan 2009" appeared. Driving me cracy. Any thing I might have miss out? -- Regards FangYR Malaysia If you enter 1/3 in A2, Excel will append the current year so you will see 3 Jan 2009 in A2. This is normal behavior. However, your formula result will be 3 Jan 2008 --ron |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date fomula not working
Hi,
The function is calculating the date one year prior with and adjustment for leap years. It works as written. However you might consider using the following which does exactly the same thing: =EDATE(A2,-12) In Excel 2003 or earlier you will need to attach the ATP - Tools, Add-Ins, and check Analysis ToolPak. In 2007 nothing to do. -- If this helps, please click the Yes button Cheers, Shane Devenshire "FangYR" wrote: I have use this last year, it's fine until now. the formula is now new to Ron, =DATE(YEAR(A2)-1,MONTH(A2),DAY(A2))-(MONTH(DATE(YEAR(A2)-1,MONTH(A2),DAY(A2)))<MONTH(A2)) That is, I enter a month and day in A2 and it will appear as last year's date, but now nothing changes, still in default year 2009. Please advise. -- Regards FangYR Malaysia |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date fomula not working
I'm pretty sure this simpler (than the original) non-ATP formula will also
work... =A2-365-(DAY(A2)<DAY(A2-365)) -- Rick (MVP - Excel) "Shane Devenshire" wrote in message ... Hi, The function is calculating the date one year prior with and adjustment for leap years. It works as written. However you might consider using the following which does exactly the same thing: =EDATE(A2,-12) In Excel 2003 or earlier you will need to attach the ATP - Tools, Add-Ins, and check Analysis ToolPak. In 2007 nothing to do. -- If this helps, please click the Yes button Cheers, Shane Devenshire "FangYR" wrote: I have use this last year, it's fine until now. the formula is now new to Ron, =DATE(YEAR(A2)-1,MONTH(A2),DAY(A2))-(MONTH(DATE(YEAR(A2)-1,MONTH(A2),DAY(A2)))<MONTH(A2)) That is, I enter a month and day in A2 and it will appear as last year's date, but now nothing changes, still in default year 2009. Please advise. -- Regards FangYR Malaysia |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date fomula not working
Good one, Rick.
Regards, Fred. "Rick Rothstein" wrote in message ... I'm pretty sure this simpler (than the original) non-ATP formula will also work... =A2-365-(DAY(A2)<DAY(A2-365)) -- Rick (MVP - Excel) "Shane Devenshire" wrote in message ... Hi, The function is calculating the date one year prior with and adjustment for leap years. It works as written. However you might consider using the following which does exactly the same thing: =EDATE(A2,-12) In Excel 2003 or earlier you will need to attach the ATP - Tools, Add-Ins, and check Analysis ToolPak. In 2007 nothing to do. -- If this helps, please click the Yes button Cheers, Shane Devenshire "FangYR" wrote: I have use this last year, it's fine until now. the formula is now new to Ron, =DATE(YEAR(A2)-1,MONTH(A2),DAY(A2))-(MONTH(DATE(YEAR(A2)-1,MONTH(A2),DAY(A2)))<MONTH(A2)) That is, I enter a month and day in A2 and it will appear as last year's date, but now nothing changes, still in default year 2009. Please advise. -- Regards FangYR Malaysia |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date fomula not working
I am back again.
Thanks to all of you. I tried according to all your advices but it did'nt work as it did before, even when trying on in older worksheets which worked fine, then. I thing somthing "add-ons" sre missing. Let me try on that, then come back to you all. Thanks again -- Regards FangYR Malaysia "Fred Smith" wrote: Good one, Rick. Regards, Fred. "Rick Rothstein" wrote in message ... I'm pretty sure this simpler (than the original) non-ATP formula will also work... =A2-365-(DAY(A2)<DAY(A2-365)) -- Rick (MVP - Excel) "Shane Devenshire" wrote in message ... Hi, The function is calculating the date one year prior with and adjustment for leap years. It works as written. However you might consider using the following which does exactly the same thing: =EDATE(A2,-12) In Excel 2003 or earlier you will need to attach the ATP - Tools, Add-Ins, and check Analysis ToolPak. In 2007 nothing to do. -- If this helps, please click the Yes button Cheers, Shane Devenshire "FangYR" wrote: I have use this last year, it's fine until now. the formula is now new to Ron, =DATE(YEAR(A2)-1,MONTH(A2),DAY(A2))-(MONTH(DATE(YEAR(A2)-1,MONTH(A2),DAY(A2)))<MONTH(A2)) That is, I enter a month and day in A2 and it will appear as last year's date, but now nothing changes, still in default year 2009. Please advise. -- Regards FangYR Malaysia |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date fomula not working
Did you try this formula (which I posted earlier)?
=A2-365-(DAY(A2)<DAY(A2-365)) I cannot find a way to not make it work, although you may want to use the formula set up this way... =IF(A2="","",A2-365-(DAY(A2)<DAY(A2-365))) so that it won't error out for empty cells (if you decide to copy it down in anticipation of future date entries). Give it a try. -- Rick (MVP - Excel) "FangYR" wrote in message ... I am back again. Thanks to all of you. I tried according to all your advices but it did'nt work as it did before, even when trying on in older worksheets which worked fine, then. I thing somthing "add-ons" sre missing. Let me try on that, then come back to you all. Thanks again -- Regards FangYR Malaysia "Fred Smith" wrote: Good one, Rick. Regards, Fred. "Rick Rothstein" wrote in message ... I'm pretty sure this simpler (than the original) non-ATP formula will also work... =A2-365-(DAY(A2)<DAY(A2-365)) -- Rick (MVP - Excel) "Shane Devenshire" wrote in message ... Hi, The function is calculating the date one year prior with and adjustment for leap years. It works as written. However you might consider using the following which does exactly the same thing: =EDATE(A2,-12) In Excel 2003 or earlier you will need to attach the ATP - Tools, Add-Ins, and check Analysis ToolPak. In 2007 nothing to do. -- If this helps, please click the Yes button Cheers, Shane Devenshire "FangYR" wrote: I have use this last year, it's fine until now. the formula is now new to Ron, =DATE(YEAR(A2)-1,MONTH(A2),DAY(A2))-(MONTH(DATE(YEAR(A2)-1,MONTH(A2),DAY(A2)))<MONTH(A2)) That is, I enter a month and day in A2 and it will appear as last year's date, but now nothing changes, still in default year 2009. Please advise. -- Regards FangYR Malaysia |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date fomula not working
Hi All,
I have checked "Add-ons", install all, yet same result. to be specific, cell G2, the formula, in A2, I enter "1/3" (ie 3th Jan) and I expect A2 to read "3 Jan 2008", but "3 Jan 2009" appeared. Driving me cracy. Any thing I might have miss out? -- Regards FangYR Malaysia "FangYR" wrote: I am back again. Thanks to all of you. I tried according to all your advices but it did'nt work as it did before, even when trying on in older worksheets which worked fine, then. I thing somthing "add-ons" sre missing. Let me try on that, then come back to you all. Thanks again -- Regards FangYR Malaysia "Fred Smith" wrote: Good one, Rick. Regards, Fred. "Rick Rothstein" wrote in message ... I'm pretty sure this simpler (than the original) non-ATP formula will also work... =A2-365-(DAY(A2)<DAY(A2-365)) -- Rick (MVP - Excel) "Shane Devenshire" wrote in message ... Hi, The function is calculating the date one year prior with and adjustment for leap years. It works as written. However you might consider using the following which does exactly the same thing: =EDATE(A2,-12) In Excel 2003 or earlier you will need to attach the ATP - Tools, Add-Ins, and check Analysis ToolPak. In 2007 nothing to do. -- If this helps, please click the Yes button Cheers, Shane Devenshire "FangYR" wrote: I have use this last year, it's fine until now. the formula is now new to Ron, =DATE(YEAR(A2)-1,MONTH(A2),DAY(A2))-(MONTH(DATE(YEAR(A2)-1,MONTH(A2),DAY(A2)))<MONTH(A2)) That is, I enter a month and day in A2 and it will appear as last year's date, but now nothing changes, still in default year 2009. Please advise. -- Regards FangYR Malaysia |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Date Fomula Question | Excel Discussion (Misc queries) | |||
| Fomula only half working | Excel Discussion (Misc queries) | |||
| Fomula for number of days on each month from a date range | Excel Discussion (Misc queries) | |||
| fomula for working out profit | Excel Discussion (Misc queries) | |||
| the date format is not working ,sort by date doesn't work. | Excel Discussion (Misc queries) | |||
Hybrid Mode
