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 |
#31
|
|||
|
|||
Date fomula not working
Hi David,
Sorry to say, your last formula didnt work either. -- Regards FangYR Malaysia "David Biddulph" wrote: Correction: =DATE(G$1,MONTH(A2),DAY(A2)) -- David Biddulph David Biddulph wrote: If you want all your dates to be shifted to a specific year, put that year in G1, for example, and make your G2 formula =DATE(YEAR(G$1),MONTH(A2),DAY(A2)) FangYR wrote: Thanks all of you for the effort. As I have stated in the beginning, that formula works before. I am entering last year's bills as a record. So, I would like to type in A2 a date (ie 1/3) that will automatically appear as dd/mmm/2008 (in cell A2, B2, C2, etc.), instead of the current year. If I have to type the full date in column A (eg. 3/1/2008), then putting a formula in column G serves no purpose. Hope I am making it clearer this time. column A =date column G =formula Going to work now, see you all later. Cheers. - - Regards FangYR Malaysia "Rick Rothstein" wrote: That number makes more sense. 39085 is a "date" to Excel... it is the number of days since January 1, 1900. Format that cell as Date and the 39045 will change to 1/3/2007. Now, the reason for the 2007 instead of 2008 is because you are subtracting 2 from the YEAR(A2) value (hence 2 years prior) instead of subtracting 1 (to get last year). By the way, if you only want last years date (the formula I'm about to give you will only work to give last year's date), then give this much simpler (and more efficient) formula a try... =A2-365-(DAY(A2)DAY(A2-365)) It subtracts the 365 days in a normal year and if the day values between the date in A2 and the date 365 days earlier don't match, then a leap year was present, so it subtracts an additional day to skip over it. Note, you will probably still have to reformat the cell to Date after entering this formula as well. -- Rick (MVP - Excel) "FangYR" wrote in message ... Sorry, this one is correct type 1/3 in A2. 39085 appeared in G2. -- Regards FangYR Malaysia "Rick Rothstein" wrote: Telling us a result without telling us your input is meaningless... exactly what did you type into A2 to get that 693231 value? -- Rick (MVP - Excel) "FangYR" wrote in message ... In G2 , =DATE(YEAR(A2)-2,MONTH(A2),DAY(A2))-(MONTH(DATE(YEAR(A2)-2,MONTH(A2),DAY(A2)))MONTH(A2)), number appeared, 693231. any idea? -- Regards FangYR Malaysia "David Biddulph" wrote: You'll need to tell us precisely what values you've got in which cells. If you use the formula =A2 and format as General, what number do you have? If you use the formula =G2 and format as General, what number do you have? -- David Biddulph "FangYR" wrote in message ... I place the formula in G2, format cell A2 as dd-mmm-yyyy. whatever date style I enter, the year is still "2009". It refuse to compute! Ai!!! -- Regards FangYR Malaysia "Max" wrote: .. in A2, I enter "1/3" (ie 3th Jan) and I expect A2 to read "3 Jan 2008", but "3 Jan 2009" appeared. well, that was precisely my point/sugegstion to you in my earlier response, A real date is a full valid, unambiguous date (day-month-year) recognized by Excel, eg: 01-Jan-2009. If you always practice entering dates as full dates, you won't get caught out with ambiguities such as what happened as you described. If you don't enter the date with the year in it - that's what you did, Excel will then assume the year is the current year (from the PC's clock), hence you get: "3 Jan 2009". You got "3 Jan 2008" previously because you entered it sometime last year (in 2008). Take my suggestion, never skimp on the data entry step when it comes to dates. Always enter it unambiguously in full, inclusive of the year, and use "mmm" format to denote the month as well in the date entry. -- Max Singapore http://savefile.com/projects/236895 Downloads:23,000 Files:370 Subscribers:66 xdemechanik --- |
#32
|
|||
|
|||
Date fomula not working
At one stage you told us that G2 contained 39508.
If you format that cell as date you will get 3-Jan-2008, as required. Also, Rick Rothstein suggested a much simpler formula giving the same result: =A2-365-(DAY(A2)DAY(A2-365)) -- Kind regards, Niek Otten Microsoft MVP - Excel "FangYR" wrote in message ... Fred wrote: " Enter all your dates in d/m format, to which Excel will add 2009. Add another column which subtracts one year. Use that column for your purposes." This is what I have been stressing all the while. In G2 this formula was inserted:=DATE(YEAR(A2)-1,MONTH(A2),DAY(A2))-(MONTH(DATE(YEAR(A2)-1,MONTH(A2),DAY(A2)))MONTH(A2)), and i expext Excel to make changes in A2 to give this "3-Jan-2008" reading. This worked last year, but not any more. Need a solution, that's all. If this formula works with your computer and not mine, I like to know where went wrong. Thanks for all your patience. -- Regards FangYR Malaysia "Fred Smith" wrote: If all you're entering is 1/3, Excel assumes the current year. How would Excel to know that you want last year, unless you told it? Defaulting to the current year is a very reasonable assumption on Excel's part, one that most people would want. If you want something different, your choices a 1. Change your computer clock to 2008. 2. Enter the extra digits for the year (ie 1/3/8) -- it's only two characters. 3. Enter all your dates in d/m format, to which Excel will add 2009. Add another column which subtracts one year. Use that column for your purposes. 4. Write a macro to capture your entered date (1/3), and change it to 2008. All in all, I think option 2 is the best, but it's up to you. Regards, Fred. "FangYR" wrote in message ... Thanks all of you for the effort. As I have stated in the beginning, that formula works before. I am entering last year's bills as a record. So, I would like to type in A2 a date (ie 1/3) that will automatically appear as dd/mmm/2008 (in cell A2, B2, C2, etc.), instead of the current year. If I have to type the full date in column A (eg. 3/1/2008), then putting a formula in column G serves no purpose. Hope I am making it clearer this time. column A =date column G =formula Going to work now, see you all later. Cheers. - - Regards FangYR Malaysia "Rick Rothstein" wrote: That number makes more sense. 39085 is a "date" to Excel... it is the number of days since January 1, 1900. Format that cell as Date and the 39045 will change to 1/3/2007. Now, the reason for the 2007 instead of 2008 is because you are subtracting 2 from the YEAR(A2) value (hence 2 years prior) instead of subtracting 1 (to get last year). By the way, if you only want last years date (the formula I'm about to give you will only work to give last year's date), then give this much simpler (and more efficient) formula a try... =A2-365-(DAY(A2)DAY(A2-365)) It subtracts the 365 days in a normal year and if the day values between the date in A2 and the date 365 days earlier don't match, then a leap year was present, so it subtracts an additional day to skip over it. Note, you will probably still have to reformat the cell to Date after entering this formula as well. -- Rick (MVP - Excel) "FangYR" wrote in message ... Sorry, this one is correct type 1/3 in A2. 39085 appeared in G2. -- Regards FangYR Malaysia "Rick Rothstein" wrote: Telling us a result without telling us your input is meaningless... exactly what did you type into A2 to get that 693231 value? -- Rick (MVP - Excel) "FangYR" wrote in message ... In G2 , =DATE(YEAR(A2)-2,MONTH(A2),DAY(A2))-(MONTH(DATE(YEAR(A2)-2,MONTH(A2),DAY(A2)))MONTH(A2)), number appeared, 693231. any idea? -- Regards FangYR Malaysia "David Biddulph" wrote: You'll need to tell us precisely what values you've got in which cells. If you use the formula =A2 and format as General, what number do you have? If you use the formula =G2 and format as General, what number do you have? -- David Biddulph "FangYR" wrote in message ... I place the formula in G2, format cell A2 as dd-mmm-yyyy. whatever date style I enter, the year is still "2009". It refuse to compute! Ai!!! -- Regards FangYR Malaysia "Max" wrote: .. in A2, I enter "1/3" (ie 3th Jan) and I expect A2 to read "3 Jan 2008", but "3 Jan 2009" appeared. well, that was precisely my point/sugegstion to you in my earlier response, A real date is a full valid, unambiguous date (day-month-year) recognized by Excel, eg: 01-Jan-2009. If you always practice entering dates as full dates, you won't get caught out with ambiguities such as what happened as you described. If you don't enter the date with the year in it - that's what you did, Excel will then assume the year is the current year (from the PC's clock), hence you get: "3 Jan 2009". You got "3 Jan 2008" previously because you entered it sometime last year (in 2008). Take my suggestion, never skimp on the data entry step when it comes to dates. Always enter it unambiguously in full, inclusive of the year, and use "mmm" format to denote the month as well in the date entry. -- Max Singapore http://savefile.com/projects/236895 Downloads:23,000 Files:370 Subscribers:66 xdemechanik --- |
#33
|
|||
|
|||
Date fomula not working
A formula in G2 will not change the value in A2.
-- David Biddulph "FangYR" wrote in message ... Fred wrote: " Enter all your dates in d/m format, to which Excel will add 2009. Add another column which subtracts one year. Use that column for your purposes." This is what I have been stressing all the while. In G2 this formula was inserted:=DATE(YEAR(A2)-1,MONTH(A2),DAY(A2))-(MONTH(DATE(YEAR(A2)-1,MONTH(A2),DAY(A2)))MONTH(A2)), and i expext Excel to make changes in A2 to give this "3-Jan-2008" reading. This worked last year, but not any more. Need a solution, that's all. If this formula works with your computer and not mine, I like to know where went wrong. Thanks for all your patience. -- Regards FangYR Malaysia "Fred Smith" wrote: If all you're entering is 1/3, Excel assumes the current year. How would Excel to know that you want last year, unless you told it? Defaulting to the current year is a very reasonable assumption on Excel's part, one that most people would want. If you want something different, your choices a 1. Change your computer clock to 2008. 2. Enter the extra digits for the year (ie 1/3/8) -- it's only two characters. 3. Enter all your dates in d/m format, to which Excel will add 2009. Add another column which subtracts one year. Use that column for your purposes. 4. Write a macro to capture your entered date (1/3), and change it to 2008. All in all, I think option 2 is the best, but it's up to you. Regards, Fred. "FangYR" wrote in message ... Thanks all of you for the effort. As I have stated in the beginning, that formula works before. I am entering last year's bills as a record. So, I would like to type in A2 a date (ie 1/3) that will automatically appear as dd/mmm/2008 (in cell A2, B2, C2, etc.), instead of the current year. If I have to type the full date in column A (eg. 3/1/2008), then putting a formula in column G serves no purpose. Hope I am making it clearer this time. column A =date column G =formula Going to work now, see you all later. Cheers. - - Regards FangYR Malaysia "Rick Rothstein" wrote: That number makes more sense. 39085 is a "date" to Excel... it is the number of days since January 1, 1900. Format that cell as Date and the 39045 will change to 1/3/2007. Now, the reason for the 2007 instead of 2008 is because you are subtracting 2 from the YEAR(A2) value (hence 2 years prior) instead of subtracting 1 (to get last year). By the way, if you only want last years date (the formula I'm about to give you will only work to give last year's date), then give this much simpler (and more efficient) formula a try... =A2-365-(DAY(A2)DAY(A2-365)) It subtracts the 365 days in a normal year and if the day values between the date in A2 and the date 365 days earlier don't match, then a leap year was present, so it subtracts an additional day to skip over it. Note, you will probably still have to reformat the cell to Date after entering this formula as well. -- Rick (MVP - Excel) "FangYR" wrote in message ... Sorry, this one is correct type 1/3 in A2. 39085 appeared in G2. -- Regards FangYR Malaysia "Rick Rothstein" wrote: Telling us a result without telling us your input is meaningless... exactly what did you type into A2 to get that 693231 value? -- Rick (MVP - Excel) "FangYR" wrote in message ... In G2 , =DATE(YEAR(A2)-2,MONTH(A2),DAY(A2))-(MONTH(DATE(YEAR(A2)-2,MONTH(A2),DAY(A2)))MONTH(A2)), number appeared, 693231. any idea? -- Regards FangYR Malaysia "David Biddulph" wrote: You'll need to tell us precisely what values you've got in which cells. If you use the formula =A2 and format as General, what number do you have? If you use the formula =G2 and format as General, what number do you have? -- David Biddulph "FangYR" wrote in message ... I place the formula in G2, format cell A2 as dd-mmm-yyyy. whatever date style I enter, the year is still "2009". It refuse to compute! Ai!!! -- Regards FangYR Malaysia "Max" wrote: .. in A2, I enter "1/3" (ie 3th Jan) and I expect A2 to read "3 Jan 2008", but "3 Jan 2009" appeared. well, that was precisely my point/sugegstion to you in my earlier response, A real date is a full valid, unambiguous date (day-month-year) recognized by Excel, eg: 01-Jan-2009. If you always practice entering dates as full dates, you won't get caught out with ambiguities such as what happened as you described. If you don't enter the date with the year in it - that's what you did, Excel will then assume the year is the current year (from the PC's clock), hence you get: "3 Jan 2009". You got "3 Jan 2008" previously because you entered it sometime last year (in 2008). Take my suggestion, never skimp on the data entry step when it comes to dates. Always enter it unambiguously in full, inclusive of the year, and use "mmm" format to denote the month as well in the date entry. -- Max Singapore http://savefile.com/projects/236895 Downloads:23,000 Files:370 Subscribers:66 xdemechanik --- |
#34
|
|||
|
|||
Date fomula not working
You have been told more than once that if you want our help you need to tell
us specific values. "didnt work" is not a sufficiently specific problem description to enable anyone other than a clairvoyant to tell you what you did wrong. You steadfastly refuse to provide the information which has been requested, so none of us can help you. We know that the formulae which we provide will work (and we have tested them), but you won't tell us what you have done, so we can't help you to sort out what mistake you have made. I'm sorry if I sound short-tempered, but this thread has been going on for a number of days with many people trying to help you, but getting nowhere because you will not provide the detailed diagnostic information which they would need if they were to help you. -- David Biddulph "FangYR" wrote in message ... Hi David, Sorry to say, your last formula didnt work either. -- Regards FangYR Malaysia "David Biddulph" wrote: Correction: =DATE(G$1,MONTH(A2),DAY(A2)) -- David Biddulph David Biddulph wrote: If you want all your dates to be shifted to a specific year, put that year in G1, for example, and make your G2 formula =DATE(YEAR(G$1),MONTH(A2),DAY(A2)) FangYR wrote: Thanks all of you for the effort. As I have stated in the beginning, that formula works before. I am entering last year's bills as a record. So, I would like to type in A2 a date (ie 1/3) that will automatically appear as dd/mmm/2008 (in cell A2, B2, C2, etc.), instead of the current year. If I have to type the full date in column A (eg. 3/1/2008), then putting a formula in column G serves no purpose. Hope I am making it clearer this time. column A =date column G =formula Going to work now, see you all later. Cheers. - - Regards FangYR Malaysia "Rick Rothstein" wrote: That number makes more sense. 39085 is a "date" to Excel... it is the number of days since January 1, 1900. Format that cell as Date and the 39045 will change to 1/3/2007. Now, the reason for the 2007 instead of 2008 is because you are subtracting 2 from the YEAR(A2) value (hence 2 years prior) instead of subtracting 1 (to get last year). By the way, if you only want last years date (the formula I'm about to give you will only work to give last year's date), then give this much simpler (and more efficient) formula a try... =A2-365-(DAY(A2)DAY(A2-365)) It subtracts the 365 days in a normal year and if the day values between the date in A2 and the date 365 days earlier don't match, then a leap year was present, so it subtracts an additional day to skip over it. Note, you will probably still have to reformat the cell to Date after entering this formula as well. -- Rick (MVP - Excel) "FangYR" wrote in message ... Sorry, this one is correct type 1/3 in A2. 39085 appeared in G2. -- Regards FangYR Malaysia "Rick Rothstein" wrote: Telling us a result without telling us your input is meaningless... exactly what did you type into A2 to get that 693231 value? -- Rick (MVP - Excel) "FangYR" wrote in message ... In G2 , =DATE(YEAR(A2)-2,MONTH(A2),DAY(A2))-(MONTH(DATE(YEAR(A2)-2,MONTH(A2),DAY(A2)))MONTH(A2)), number appeared, 693231. any idea? -- Regards FangYR Malaysia "David Biddulph" wrote: You'll need to tell us precisely what values you've got in which cells. If you use the formula =A2 and format as General, what number do you have? If you use the formula =G2 and format as General, what number do you have? -- David Biddulph "FangYR" wrote in message ... I place the formula in G2, format cell A2 as dd-mmm-yyyy. whatever date style I enter, the year is still "2009". It refuse to compute! Ai!!! -- Regards FangYR Malaysia "Max" wrote: .. in A2, I enter "1/3" (ie 3th Jan) and I expect A2 to read "3 Jan 2008", but "3 Jan 2009" appeared. well, that was precisely my point/sugegstion to you in my earlier response, A real date is a full valid, unambiguous date (day-month-year) recognized by Excel, eg: 01-Jan-2009. If you always practice entering dates as full dates, you won't get caught out with ambiguities such as what happened as you described. If you don't enter the date with the year in it - that's what you did, Excel will then assume the year is the current year (from the PC's clock), hence you get: "3 Jan 2009". You got "3 Jan 2008" previously because you entered it sometime last year (in 2008). Take my suggestion, never skimp on the data entry step when it comes to dates. Always enter it unambiguously in full, inclusive of the year, and use "mmm" format to denote the month as well in the date entry. -- Max Singapore http://savefile.com/projects/236895 Downloads:23,000 Files:370 Subscribers:66 xdemechanik --- |
#35
|
|||
|
|||
Date fomula not working
On Tue, 17 Feb 2009 02:15:01 -0800, FangYR
wrote: In G2 this formula was inserted:=DATE(YEAR(A2)-1,MONTH(A2),DAY(A2))-(MONTH(DATE(YEAR(A2)-1,MONTH(A2),DAY(A2)))MONTH(A2)), and i expext Excel to make changes in A2 to give this "3-Jan-2008" reading. A formula in G2 cannot change the value in A2. --ron |
#36
|
|||
|
|||
Date fomula not working
Ok David,
I open a new workbook. 1) Format A2 as Date (14-Mar-01, as in dialogue box). 2) Insert formula in G2 which gives a number 693596 (A2 no data yet). 3) Type 1/3 in A2 and it reads 3-Jan-09. The above is the simpliest way to state my case. As I said earlier, it worked last year when I got this formula from Ron. -- Regards FangYR Malaysia "David Biddulph" wrote: You have been told more than once that if you want our help you need to tell us specific values. "didnt work" is not a sufficiently specific problem description to enable anyone other than a clairvoyant to tell you what you did wrong. You steadfastly refuse to provide the information which has been requested, so none of us can help you. We know that the formulae which we provide will work (and we have tested them), but you won't tell us what you have done, so we can't help you to sort out what mistake you have made. I'm sorry if I sound short-tempered, but this thread has been going on for a number of days with many people trying to help you, but getting nowhere because you will not provide the detailed diagnostic information which they would need if they were to help you. -- David Biddulph "FangYR" wrote in message ... Hi David, Sorry to say, your last formula didnt work either. -- Regards FangYR Malaysia "David Biddulph" wrote: Correction: =DATE(G$1,MONTH(A2),DAY(A2)) -- David Biddulph David Biddulph wrote: If you want all your dates to be shifted to a specific year, put that year in G1, for example, and make your G2 formula =DATE(YEAR(G$1),MONTH(A2),DAY(A2)) FangYR wrote: Thanks all of you for the effort. As I have stated in the beginning, that formula works before. I am entering last year's bills as a record. So, I would like to type in A2 a date (ie 1/3) that will automatically appear as dd/mmm/2008 (in cell A2, B2, C2, etc.), instead of the current year. If I have to type the full date in column A (eg. 3/1/2008), then putting a formula in column G serves no purpose. Hope I am making it clearer this time. column A =date column G =formula Going to work now, see you all later. Cheers. - - Regards FangYR Malaysia "Rick Rothstein" wrote: That number makes more sense. 39085 is a "date" to Excel... it is the number of days since January 1, 1900. Format that cell as Date and the 39045 will change to 1/3/2007. Now, the reason for the 2007 instead of 2008 is because you are subtracting 2 from the YEAR(A2) value (hence 2 years prior) instead of subtracting 1 (to get last year). By the way, if you only want last years date (the formula I'm about to give you will only work to give last year's date), then give this much simpler (and more efficient) formula a try... =A2-365-(DAY(A2)DAY(A2-365)) It subtracts the 365 days in a normal year and if the day values between the date in A2 and the date 365 days earlier don't match, then a leap year was present, so it subtracts an additional day to skip over it. Note, you will probably still have to reformat the cell to Date after entering this formula as well. -- Rick (MVP - Excel) "FangYR" wrote in message ... Sorry, this one is correct type 1/3 in A2. 39085 appeared in G2. -- Regards FangYR Malaysia "Rick Rothstein" wrote: Telling us a result without telling us your input is meaningless... exactly what did you type into A2 to get that 693231 value? -- Rick (MVP - Excel) "FangYR" wrote in message ... In G2 , =DATE(YEAR(A2)-2,MONTH(A2),DAY(A2))-(MONTH(DATE(YEAR(A2)-2,MONTH(A2),DAY(A2)))MONTH(A2)), number appeared, 693231. any idea? -- Regards FangYR Malaysia "David Biddulph" wrote: You'll need to tell us precisely what values you've got in which cells. If you use the formula =A2 and format as General, what number do you have? If you use the formula =G2 and format as General, what number do you have? -- David Biddulph "FangYR" wrote in message ... I place the formula in G2, format cell A2 as dd-mmm-yyyy. whatever date style I enter, the year is still "2009". It refuse to compute! Ai!!! -- Regards FangYR Malaysia "Max" wrote: .. in A2, I enter "1/3" (ie 3th Jan) and I expect A2 to read "3 Jan 2008", but "3 Jan 2009" appeared. well, that was precisely my point/sugegstion to you in my earlier response, A real date is a full valid, unambiguous date (day-month-year) recognized by Excel, eg: 01-Jan-2009. If you always practice entering dates as full dates, you won't get caught out with ambiguities such as what happened as you described. If you don't enter the date with the year in it - that's what you did, Excel will then assume the year is the current year (from the PC's clock), hence you get: "3 Jan 2009". You got "3 Jan 2008" previously because you entered it sometime last year (in 2008). Take my suggestion, never skimp on the data entry step when it comes to dates. Always enter it unambiguously in full, inclusive of the year, and use "mmm" format to denote the month as well in the date entry. -- Max Singapore http://savefile.com/projects/236895 Downloads:23,000 Files:370 Subscribers:66 xdemechanik --- |
#37
|
|||
|
|||
Date fomula not working
one more thing.
4) G2 reads 39450 -- Regards FangYR Malaysia "David Biddulph" wrote: You have been told more than once that if you want our help you need to tell us specific values. "didnt work" is not a sufficiently specific problem description to enable anyone other than a clairvoyant to tell you what you did wrong. You steadfastly refuse to provide the information which has been requested, so none of us can help you. We know that the formulae which we provide will work (and we have tested them), but you won't tell us what you have done, so we can't help you to sort out what mistake you have made. I'm sorry if I sound short-tempered, but this thread has been going on for a number of days with many people trying to help you, but getting nowhere because you will not provide the detailed diagnostic information which they would need if they were to help you. -- David Biddulph "FangYR" wrote in message ... Hi David, Sorry to say, your last formula didnt work either. -- Regards FangYR Malaysia "David Biddulph" wrote: Correction: =DATE(G$1,MONTH(A2),DAY(A2)) -- David Biddulph David Biddulph wrote: If you want all your dates to be shifted to a specific year, put that year in G1, for example, and make your G2 formula =DATE(YEAR(G$1),MONTH(A2),DAY(A2)) FangYR wrote: Thanks all of you for the effort. As I have stated in the beginning, that formula works before. I am entering last year's bills as a record. So, I would like to type in A2 a date (ie 1/3) that will automatically appear as dd/mmm/2008 (in cell A2, B2, C2, etc.), instead of the current year. If I have to type the full date in column A (eg. 3/1/2008), then putting a formula in column G serves no purpose. Hope I am making it clearer this time. column A =date column G =formula Going to work now, see you all later. Cheers. - - Regards FangYR Malaysia "Rick Rothstein" wrote: That number makes more sense. 39085 is a "date" to Excel... it is the number of days since January 1, 1900. Format that cell as Date and the 39045 will change to 1/3/2007. Now, the reason for the 2007 instead of 2008 is because you are subtracting 2 from the YEAR(A2) value (hence 2 years prior) instead of subtracting 1 (to get last year). By the way, if you only want last years date (the formula I'm about to give you will only work to give last year's date), then give this much simpler (and more efficient) formula a try... =A2-365-(DAY(A2)DAY(A2-365)) It subtracts the 365 days in a normal year and if the day values between the date in A2 and the date 365 days earlier don't match, then a leap year was present, so it subtracts an additional day to skip over it. Note, you will probably still have to reformat the cell to Date after entering this formula as well. -- Rick (MVP - Excel) "FangYR" wrote in message ... Sorry, this one is correct type 1/3 in A2. 39085 appeared in G2. -- Regards FangYR Malaysia "Rick Rothstein" wrote: Telling us a result without telling us your input is meaningless... exactly what did you type into A2 to get that 693231 value? -- Rick (MVP - Excel) "FangYR" wrote in message ... In G2 , =DATE(YEAR(A2)-2,MONTH(A2),DAY(A2))-(MONTH(DATE(YEAR(A2)-2,MONTH(A2),DAY(A2)))MONTH(A2)), number appeared, 693231. any idea? -- Regards FangYR Malaysia "David Biddulph" wrote: You'll need to tell us precisely what values you've got in which cells. If you use the formula =A2 and format as General, what number do you have? If you use the formula =G2 and format as General, what number do you have? -- David Biddulph "FangYR" wrote in message ... I place the formula in G2, format cell A2 as dd-mmm-yyyy. whatever date style I enter, the year is still "2009". It refuse to compute! Ai!!! -- Regards FangYR Malaysia "Max" wrote: .. in A2, I enter "1/3" (ie 3th Jan) and I expect A2 to read "3 Jan 2008", but "3 Jan 2009" appeared. well, that was precisely my point/sugegstion to you in my earlier response, A real date is a full valid, unambiguous date (day-month-year) recognized by Excel, eg: 01-Jan-2009. If you always practice entering dates as full dates, you won't get caught out with ambiguities such as what happened as you described. If you don't enter the date with the year in it - that's what you did, Excel will then assume the year is the current year (from the PC's clock), hence you get: "3 Jan 2009". You got "3 Jan 2008" previously because you entered it sometime last year (in 2008). Take my suggestion, never skimp on the data entry step when it comes to dates. Always enter it unambiguously in full, inclusive of the year, and use "mmm" format to denote the month as well in the date entry. -- Max Singapore http://savefile.com/projects/236895 Downloads:23,000 Files:370 Subscribers:66 xdemechanik --- |
#38
|
|||
|
|||
Date fomula not working
Thta is March 1 2008, so you probably typed 3/1 in A2 instead of 1/3.
Anyway, both result in a date in 2008, not 2009 -- Kind regards, Niek Otten Microsoft MVP - Excel "FangYR" wrote in message ... one more thing. 4) G2 reads 39450 -- Regards FangYR Malaysia "David Biddulph" wrote: You have been told more than once that if you want our help you need to tell us specific values. "didnt work" is not a sufficiently specific problem description to enable anyone other than a clairvoyant to tell you what you did wrong. You steadfastly refuse to provide the information which has been requested, so none of us can help you. We know that the formulae which we provide will work (and we have tested them), but you won't tell us what you have done, so we can't help you to sort out what mistake you have made. I'm sorry if I sound short-tempered, but this thread has been going on for a number of days with many people trying to help you, but getting nowhere because you will not provide the detailed diagnostic information which they would need if they were to help you. -- David Biddulph "FangYR" wrote in message ... Hi David, Sorry to say, your last formula didnt work either. -- Regards FangYR Malaysia "David Biddulph" wrote: Correction: =DATE(G$1,MONTH(A2),DAY(A2)) -- David Biddulph David Biddulph wrote: If you want all your dates to be shifted to a specific year, put that year in G1, for example, and make your G2 formula =DATE(YEAR(G$1),MONTH(A2),DAY(A2)) FangYR wrote: Thanks all of you for the effort. As I have stated in the beginning, that formula works before. I am entering last year's bills as a record. So, I would like to type in A2 a date (ie 1/3) that will automatically appear as dd/mmm/2008 (in cell A2, B2, C2, etc.), instead of the current year. If I have to type the full date in column A (eg. 3/1/2008), then putting a formula in column G serves no purpose. Hope I am making it clearer this time. column A =date column G =formula Going to work now, see you all later. Cheers. - - Regards FangYR Malaysia "Rick Rothstein" wrote: That number makes more sense. 39085 is a "date" to Excel... it is the number of days since January 1, 1900. Format that cell as Date and the 39045 will change to 1/3/2007. Now, the reason for the 2007 instead of 2008 is because you are subtracting 2 from the YEAR(A2) value (hence 2 years prior) instead of subtracting 1 (to get last year). By the way, if you only want last years date (the formula I'm about to give you will only work to give last year's date), then give this much simpler (and more efficient) formula a try... =A2-365-(DAY(A2)DAY(A2-365)) It subtracts the 365 days in a normal year and if the day values between the date in A2 and the date 365 days earlier don't match, then a leap year was present, so it subtracts an additional day to skip over it. Note, you will probably still have to reformat the cell to Date after entering this formula as well. -- Rick (MVP - Excel) "FangYR" wrote in message ... Sorry, this one is correct type 1/3 in A2. 39085 appeared in G2. -- Regards FangYR Malaysia "Rick Rothstein" wrote: Telling us a result without telling us your input is meaningless... exactly what did you type into A2 to get that 693231 value? -- Rick (MVP - Excel) "FangYR" wrote in message ... In G2 , =DATE(YEAR(A2)-2,MONTH(A2),DAY(A2))-(MONTH(DATE(YEAR(A2)-2,MONTH(A2),DAY(A2)))MONTH(A2)), number appeared, 693231. any idea? -- Regards FangYR Malaysia "David Biddulph" wrote: You'll need to tell us precisely what values you've got in which cells. If you use the formula =A2 and format as General, what number do you have? If you use the formula =G2 and format as General, what number do you have? -- David Biddulph "FangYR" wrote in message ... I place the formula in G2, format cell A2 as dd-mmm-yyyy. whatever date style I enter, the year is still "2009". It refuse to compute! Ai!!! -- Regards FangYR Malaysia "Max" wrote: .. in A2, I enter "1/3" (ie 3th Jan) and I expect A2 to read "3 Jan 2008", but "3 Jan 2009" appeared. well, that was precisely my point/sugegstion to you in my earlier response, A real date is a full valid, unambiguous date (day-month-year) recognized by Excel, eg: 01-Jan-2009. If you always practice entering dates as full dates, you won't get caught out with ambiguities such as what happened as you described. If you don't enter the date with the year in it - that's what you did, Excel will then assume the year is the current year (from the PC's clock), hence you get: "3 Jan 2009". You got "3 Jan 2008" previously because you entered it sometime last year (in 2008). Take my suggestion, never skimp on the data entry step when it comes to dates. Always enter it unambiguously in full, inclusive of the year, and use "mmm" format to denote the month as well in the date entry. -- Max Singapore http://savefile.com/projects/236895 Downloads:23,000 Files:370 Subscribers:66 xdemechanik --- |
#39
|
|||
|
|||
Date fomula not working
I think you'll find that 39450 is 3 Jan 2008, not 1 Mar 2008, Niek, but yes,
you are right, the formula in G2 is returning the value that the OP wanted, in 2008, so hopefully this saga is over. -- David Biddulph "Niek Otten" wrote in message ... Thta is March 1 2008, so you probably typed 3/1 in A2 instead of 1/3. Anyway, both result in a date in 2008, not 2009 -- Kind regards, Niek Otten Microsoft MVP - Excel "FangYR" wrote in message ... one more thing. 4) G2 reads 39450 -- Regards FangYR Malaysia "David Biddulph" wrote: You have been told more than once that if you want our help you need to tell us specific values. "didnt work" is not a sufficiently specific problem description to enable anyone other than a clairvoyant to tell you what you did wrong. You steadfastly refuse to provide the information which has been requested, so none of us can help you. We know that the formulae which we provide will work (and we have tested them), but you won't tell us what you have done, so we can't help you to sort out what mistake you have made. I'm sorry if I sound short-tempered, but this thread has been going on for a number of days with many people trying to help you, but getting nowhere because you will not provide the detailed diagnostic information which they would need if they were to help you. -- David Biddulph "FangYR" wrote in message ... Hi David, Sorry to say, your last formula didnt work either. -- Regards FangYR Malaysia "David Biddulph" wrote: Correction: =DATE(G$1,MONTH(A2),DAY(A2)) -- David Biddulph David Biddulph wrote: If you want all your dates to be shifted to a specific year, put that year in G1, for example, and make your G2 formula =DATE(YEAR(G$1),MONTH(A2),DAY(A2)) FangYR wrote: Thanks all of you for the effort. As I have stated in the beginning, that formula works before. I am entering last year's bills as a record. So, I would like to type in A2 a date (ie 1/3) that will automatically appear as dd/mmm/2008 (in cell A2, B2, C2, etc.), instead of the current year. If I have to type the full date in column A (eg. 3/1/2008), then putting a formula in column G serves no purpose. Hope I am making it clearer this time. column A =date column G =formula Going to work now, see you all later. Cheers. - - Regards FangYR Malaysia "Rick Rothstein" wrote: That number makes more sense. 39085 is a "date" to Excel... it is the number of days since January 1, 1900. Format that cell as Date and the 39045 will change to 1/3/2007. Now, the reason for the 2007 instead of 2008 is because you are subtracting 2 from the YEAR(A2) value (hence 2 years prior) instead of subtracting 1 (to get last year). By the way, if you only want last years date (the formula I'm about to give you will only work to give last year's date), then give this much simpler (and more efficient) formula a try... =A2-365-(DAY(A2)DAY(A2-365)) It subtracts the 365 days in a normal year and if the day values between the date in A2 and the date 365 days earlier don't match, then a leap year was present, so it subtracts an additional day to skip over it. Note, you will probably still have to reformat the cell to Date after entering this formula as well. -- Rick (MVP - Excel) "FangYR" wrote in message ... Sorry, this one is correct type 1/3 in A2. 39085 appeared in G2. -- Regards FangYR Malaysia "Rick Rothstein" wrote: Telling us a result without telling us your input is meaningless... exactly what did you type into A2 to get that 693231 value? -- Rick (MVP - Excel) "FangYR" wrote in message ... In G2 , =DATE(YEAR(A2)-2,MONTH(A2),DAY(A2))-(MONTH(DATE(YEAR(A2)-2,MONTH(A2),DAY(A2)))MONTH(A2)), number appeared, 693231. any idea? -- Regards FangYR Malaysia "David Biddulph" wrote: You'll need to tell us precisely what values you've got in which cells. If you use the formula =A2 and format as General, what number do you have? If you use the formula =G2 and format as General, what number do you have? -- David Biddulph "FangYR" wrote in message ... I place the formula in G2, format cell A2 as dd-mmm-yyyy. whatever date style I enter, the year is still "2009". It refuse to compute! Ai!!! -- Regards FangYR Malaysia "Max" wrote: .. in A2, I enter "1/3" (ie 3th Jan) and I expect A2 to read "3 Jan 2008", but "3 Jan 2009" appeared. well, that was precisely my point/sugegstion to you in my earlier response, A real date is a full valid, unambiguous date (day-month-year) recognized by Excel, eg: 01-Jan-2009. If you always practice entering dates as full dates, you won't get caught out with ambiguities such as what happened as you described. If you don't enter the date with the year in it - that's what you did, Excel will then assume the year is the current year (from the PC's clock), hence you get: "3 Jan 2009". You got "3 Jan 2008" previously because you entered it sometime last year (in 2008). Take my suggestion, never skimp on the data entry step when it comes to dates. Always enter it unambiguously in full, inclusive of the year, and use "mmm" format to denote the month as well in the date entry. -- Max Singapore http://savefile.com/projects/236895 Downloads:23,000 Files:370 Subscribers:66 xdemechanik --- |
#40
|
|||
|
|||
Date fomula not working
Thanks David,
This thread seems to attract many errors! -- Kind regards, Niek Otten Microsoft MVP - Excel "David Biddulph" groups [at] biddulph.org.uk wrote in message ... I think you'll find that 39450 is 3 Jan 2008, not 1 Mar 2008, Niek, but yes, you are right, the formula in G2 is returning the value that the OP wanted, in 2008, so hopefully this saga is over. -- David Biddulph "Niek Otten" wrote in message ... Thta is March 1 2008, so you probably typed 3/1 in A2 instead of 1/3. Anyway, both result in a date in 2008, not 2009 -- Kind regards, Niek Otten Microsoft MVP - Excel "FangYR" wrote in message ... one more thing. 4) G2 reads 39450 -- Regards FangYR Malaysia "David Biddulph" wrote: You have been told more than once that if you want our help you need to tell us specific values. "didnt work" is not a sufficiently specific problem description to enable anyone other than a clairvoyant to tell you what you did wrong. You steadfastly refuse to provide the information which has been requested, so none of us can help you. We know that the formulae which we provide will work (and we have tested them), but you won't tell us what you have done, so we can't help you to sort out what mistake you have made. I'm sorry if I sound short-tempered, but this thread has been going on for a number of days with many people trying to help you, but getting nowhere because you will not provide the detailed diagnostic information which they would need if they were to help you. -- David Biddulph "FangYR" wrote in message ... Hi David, Sorry to say, your last formula didnt work either. -- Regards FangYR Malaysia "David Biddulph" wrote: Correction: =DATE(G$1,MONTH(A2),DAY(A2)) -- David Biddulph David Biddulph wrote: If you want all your dates to be shifted to a specific year, put that year in G1, for example, and make your G2 formula =DATE(YEAR(G$1),MONTH(A2),DAY(A2)) FangYR wrote: Thanks all of you for the effort. As I have stated in the beginning, that formula works before. I am entering last year's bills as a record. So, I would like to type in A2 a date (ie 1/3) that will automatically appear as dd/mmm/2008 (in cell A2, B2, C2, etc.), instead of the current year. If I have to type the full date in column A (eg. 3/1/2008), then putting a formula in column G serves no purpose. Hope I am making it clearer this time. column A =date column G =formula Going to work now, see you all later. Cheers. - - Regards FangYR Malaysia "Rick Rothstein" wrote: That number makes more sense. 39085 is a "date" to Excel... it is the number of days since January 1, 1900. Format that cell as Date and the 39045 will change to 1/3/2007. Now, the reason for the 2007 instead of 2008 is because you are subtracting 2 from the YEAR(A2) value (hence 2 years prior) instead of subtracting 1 (to get last year). By the way, if you only want last years date (the formula I'm about to give you will only work to give last year's date), then give this much simpler (and more efficient) formula a try... =A2-365-(DAY(A2)DAY(A2-365)) It subtracts the 365 days in a normal year and if the day values between the date in A2 and the date 365 days earlier don't match, then a leap year was present, so it subtracts an additional day to skip over it. Note, you will probably still have to reformat the cell to Date after entering this formula as well. -- Rick (MVP - Excel) "FangYR" wrote in message ... Sorry, this one is correct type 1/3 in A2. 39085 appeared in G2. -- Regards FangYR Malaysia "Rick Rothstein" wrote: Telling us a result without telling us your input is meaningless... exactly what did you type into A2 to get that 693231 value? -- Rick (MVP - Excel) "FangYR" wrote in message ... In G2 , =DATE(YEAR(A2)-2,MONTH(A2),DAY(A2))-(MONTH(DATE(YEAR(A2)-2,MONTH(A2),DAY(A2)))MONTH(A2)), number appeared, 693231. any idea? -- Regards FangYR Malaysia "David Biddulph" wrote: You'll need to tell us precisely what values you've got in which cells. If you use the formula =A2 and format as General, what number do you have? If you use the formula =G2 and format as General, what number do you have? -- David Biddulph "FangYR" wrote in message ... I place the formula in G2, format cell A2 as dd-mmm-yyyy. whatever date style I enter, the year is still "2009". It refuse to compute! Ai!!! -- Regards FangYR Malaysia "Max" wrote: .. in A2, I enter "1/3" (ie 3th Jan) and I expect A2 to read "3 Jan 2008", but "3 Jan 2009" appeared. well, that was precisely my point/sugegstion to you in my earlier response, A real date is a full valid, unambiguous date (day-month-year) recognized by Excel, eg: 01-Jan-2009. If you always practice entering dates as full dates, you won't get caught out with ambiguities such as what happened as you described. If you don't enter the date with the year in it - that's what you did, Excel will then assume the year is the current year (from the PC's clock), hence you get: "3 Jan 2009". You got "3 Jan 2008" previously because you entered it sometime last year (in 2008). Take my suggestion, never skimp on the data entry step when it comes to dates. Always enter it unambiguously in full, inclusive of the year, and use "mmm" format to denote the month as well in the date entry. -- Max Singapore http://savefile.com/projects/236895 Downloads:23,000 Files:370 Subscribers:66 xdemechanik --- |
Thread Tools | |
Display Modes | |
|
|