Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Error calculating date
hi,
please help me to resolve this issue, the following formula works fine till 31 Dec 2005, when year changes to 2006 it just stop caculating the date, formula I'm using to caculate date & year is: =IF(YEAR($E$2)-YEAR(E9)=2,IF(12-MONTH(E9)0,12-MONTH(E9)+IF(DAY($E$2)=DAY(E9),MONTH($E$2),0),MON TH($E$2)),0) Date 25/01/2007 (<--- This is the current date) D 28 December 2005 P5 1 D 01 December 2005 P3 1 D 15 December 2005 P4 1 D 13 January 2006 P4 0 (<----This Value always came wrong) D 25 October 2005 P2 3 D 01 December 2005 P5 1 D 29 October 2005 P5 2 D 22 December 2005 P1 1 D 31 December 2005 P3 1 Please help me out... |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Error calculating date
Hi
It gives 0 because it fails on the first test Year($E$2)-Year(E9) =1 Maybe you could use =MIN(12,MONTH(E2)-MONTH(E9)+12) -- Regards Roger Govier "Attie" wrote in message ... hi, please help me to resolve this issue, the following formula works fine till 31 Dec 2005, when year changes to 2006 it just stop caculating the date, formula I'm using to caculate date & year is: =IF(YEAR($E$2)-YEAR(E9)=2,IF(12-MONTH(E9)0,12-MONTH(E9)+IF(DAY($E$2)=DAY(E9),MONTH($E$2),0),MON TH($E$2)),0) Date 25/01/2007 (<--- This is the current date) D 28 December 2005 P5 1 D 01 December 2005 P3 1 D 15 December 2005 P4 1 D 13 January 2006 P4 0 (<----This Value always came wrong) D 25 October 2005 P2 3 D 01 December 2005 P5 1 D 29 October 2005 P5 2 D 22 December 2005 P1 1 D 31 December 2005 P3 1 Please help me out... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF and Date Functions | Excel Worksheet Functions | |||
insert date | Excel Worksheet Functions | |||
Insert Automatic, Non-Updating Date Stamp | Excel Discussion (Misc queries) | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
Another Date issue. | Excel Worksheet Functions |