| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculate difference between 2 dates
Hi
I have a tracking spreadsheet that contains all the deals with the close date change.. there's a close date old value in column A and a close date new value in column B is there a formula where i can use to determine if the change of date is within the quarter; pull in or push out of quarter ? Column A Column B Formula 28-Nov-2008 12-Sep-2008 Pull in 28-Nov-2008 20-Oct-2008 within the quarter 28-Nov-2008 10-Jan-2009 Push out of quarter Any help is appreciated, Thanks! -- nikko |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
calculate difference between 2 dates
=IF(YEAR(A2)*10+INT((MONTH(A2)+2)/3)=YEAR(B2)*10+INT((MONTH(B2)+2)/3),"Within
the quarter", IF(YEAR(A2)*10+INT((MONTH(A2)+2)/3)YEAR(B2)*10+INT((MONTH(B2)+2)/3),"Pull in","Push out of quarter")) -- __________________________________ HTH Bob "nikko" wrote in message ... Hi I have a tracking spreadsheet that contains all the deals with the close date change.. there's a close date old value in column A and a close date new value in column B is there a formula where i can use to determine if the change of date is within the quarter; pull in or push out of quarter ? Column A Column B Formula 28-Nov-2008 12-Sep-2008 Pull in 28-Nov-2008 20-Oct-2008 within the quarter 28-Nov-2008 10-Jan-2009 Push out of quarter Any help is appreciated, Thanks! -- nikko |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
calculate difference between 2 dates
Bob, i copied the formula you provided earlier and the result is #value!
except for one item ... it shows push out of quarter but when i take a further look; the old n new values are within the quarter (same month) .. Old Value New Value Formula 28/11/2008 08/09/2008 #VALUE! 30/09/2008 28/11/2008 #VALUE! 28/11/2008 18/08/2008 #VALUE! 22/08/2008 29/09/2008 #VALUE! 22/12/2008 18/08/2008 #VALUE! 30/07/2008 18/08/2008 #VALUE! 24/11/2008 30/09/2008 #VALUE! 30/09/2008 24/11/2008 #VALUE! 29/08/2008 30/09/2008 #VALUE! 18/07/2008 18/08/2008 #VALUE! 28/11/2008 18/08/2008 #VALUE! 27/09/2008 19/08/2008 #VALUE! 29/08/2008 19/09/2008 #VALUE! 17/10/2008 26/08/2008 #VALUE! 30/05/2008 26/08/2008 #VALUE! 29/08/2008 26/09/2008 #VALUE! 26/08/2008 01/07/2008 #VALUE! 29/08/2008 11/09/2008 #VALUE! 11/08/2008 29/08/2008 #VALUE! 18/08/2008 24/08/2008 #VALUE! 11/07/2008 18/08/2008 #VALUE! 24/09/2008 26/09/2008 #VALUE! 11/07/2008 26/08/2008 #VALUE! 28/08/2008 24/09/2008 #VALUE! 27/08/2008 24/09/2008 #VALUE! 29/08/2008 19/09/2008 #VALUE! 18/08/2008 05/08/2008 #VALUE! 22/09/2008 30/09/2008 #VALUE! 01/09/2008 04/09/2008 Push out of quarter 30/09/2008 19/09/2008 #VALUE! 30/09/2008 04/09/2008 #VALUE! -- nikko "Bob Phillips" wrote: =IF(YEAR(A2)*10+INT((MONTH(A2)+2)/3)=YEAR(B2)*10+INT((MONTH(B2)+2)/3),"Within the quarter", IF(YEAR(A2)*10+INT((MONTH(A2)+2)/3)YEAR(B2)*10+INT((MONTH(B2)+2)/3),"Pull in","Push out of quarter")) -- __________________________________ HTH Bob "nikko" wrote in message ... Hi I have a tracking spreadsheet that contains all the deals with the close date change.. there's a close date old value in column A and a close date new value in column B is there a formula where i can use to determine if the change of date is within the quarter; pull in or push out of quarter ? Column A Column B Formula 28-Nov-2008 12-Sep-2008 Pull in 28-Nov-2008 20-Oct-2008 within the quarter 28-Nov-2008 10-Jan-2009 Push out of quarter Any help is appreciated, Thanks! -- nikko |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
calculate difference between 2 dates
I have just tried it with that data and I get
Old Value New Value Formula 28/11/2008 08/09/2008 Pull in 30/09/2008 28/11/2008 Push out of quarter 28/11/2008 18/08/2008 Pull in 22/08/2008 29/09/2008 Within the quarter 22/12/2008 18/08/2008 Pull in 30/07/2008 18/08/2008 Within the quarter 24/11/2008 30/09/2008 Pull in 30/09/2008 24/11/2008 Push out of quarter 29/08/2008 30/09/2008 Within the quarter 18/07/2008 18/08/2008 Within the quarter 28/11/2008 18/08/2008 Pull in 27/09/2008 19/08/2008 Within the quarter 29/08/2008 19/09/2008 Within the quarter 17/10/2008 26/08/2008 Pull in 30/05/2008 26/08/2008 Push out of quarter 29/08/2008 26/09/2008 Within the quarter 26/08/2008 07/01/2008 Pull in 29/08/2008 09/11/2008 Push out of quarter 08/11/2008 29/08/2008 Pull in 18/08/2008 24/08/2008 Within the quarter 07/11/2008 18/08/2008 Pull in 24/09/2008 26/09/2008 Within the quarter 07/11/2008 26/08/2008 Pull in 28/08/2008 24/09/2008 Within the quarter 27/08/2008 24/09/2008 Within the quarter 29/08/2008 19/09/2008 Within the quarter 18/08/2008 08/05/2008 Pull in 22/09/2008 30/09/2008 Within the quarter 09/01/2008 09/04/2008 Push out of quarter 30/09/2008 19/09/2008 Within the quarter 30/09/2008 09/04/2008 Pull in Are you sure that those are real dates? -- __________________________________ HTH Bob "nikko" wrote in message ... Bob, i copied the formula you provided earlier and the result is #value! except for one item ... it shows push out of quarter but when i take a further look; the old n new values are within the quarter (same month) .. Old Value New Value Formula 28/11/2008 08/09/2008 #VALUE! 30/09/2008 28/11/2008 #VALUE! 28/11/2008 18/08/2008 #VALUE! 22/08/2008 29/09/2008 #VALUE! 22/12/2008 18/08/2008 #VALUE! 30/07/2008 18/08/2008 #VALUE! 24/11/2008 30/09/2008 #VALUE! 30/09/2008 24/11/2008 #VALUE! 29/08/2008 30/09/2008 #VALUE! 18/07/2008 18/08/2008 #VALUE! 28/11/2008 18/08/2008 #VALUE! 27/09/2008 19/08/2008 #VALUE! 29/08/2008 19/09/2008 #VALUE! 17/10/2008 26/08/2008 #VALUE! 30/05/2008 26/08/2008 #VALUE! 29/08/2008 26/09/2008 #VALUE! 26/08/2008 01/07/2008 #VALUE! 29/08/2008 11/09/2008 #VALUE! 11/08/2008 29/08/2008 #VALUE! 18/08/2008 24/08/2008 #VALUE! 11/07/2008 18/08/2008 #VALUE! 24/09/2008 26/09/2008 #VALUE! 11/07/2008 26/08/2008 #VALUE! 28/08/2008 24/09/2008 #VALUE! 27/08/2008 24/09/2008 #VALUE! 29/08/2008 19/09/2008 #VALUE! 18/08/2008 05/08/2008 #VALUE! 22/09/2008 30/09/2008 #VALUE! 01/09/2008 04/09/2008 Push out of quarter 30/09/2008 19/09/2008 #VALUE! 30/09/2008 04/09/2008 #VALUE! -- nikko "Bob Phillips" wrote: =IF(YEAR(A2)*10+INT((MONTH(A2)+2)/3)=YEAR(B2)*10+INT((MONTH(B2)+2)/3),"Within the quarter", IF(YEAR(A2)*10+INT((MONTH(A2)+2)/3)YEAR(B2)*10+INT((MONTH(B2)+2)/3),"Pull in","Push out of quarter")) -- __________________________________ HTH Bob "nikko" wrote in message ... Hi I have a tracking spreadsheet that contains all the deals with the close date change.. there's a close date old value in column A and a close date new value in column B is there a formula where i can use to determine if the change of date is within the quarter; pull in or push out of quarter ? Column A Column B Formula 28-Nov-2008 12-Sep-2008 Pull in 28-Nov-2008 20-Oct-2008 within the quarter 28-Nov-2008 10-Jan-2009 Push out of quarter Any help is appreciated, Thanks! -- nikko |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
calculate difference between 2 dates
bob, how do i ensure the dates are real dates?
i have selected the columns, right-click and select format cells and ensure that the date category is selected but i still get the #value error.. -- nikko "Bob Phillips" wrote: I have just tried it with that data and I get Old Value New Value Formula 28/11/2008 08/09/2008 Pull in 30/09/2008 28/11/2008 Push out of quarter 28/11/2008 18/08/2008 Pull in 22/08/2008 29/09/2008 Within the quarter 22/12/2008 18/08/2008 Pull in 30/07/2008 18/08/2008 Within the quarter 24/11/2008 30/09/2008 Pull in 30/09/2008 24/11/2008 Push out of quarter 29/08/2008 30/09/2008 Within the quarter 18/07/2008 18/08/2008 Within the quarter 28/11/2008 18/08/2008 Pull in 27/09/2008 19/08/2008 Within the quarter 29/08/2008 19/09/2008 Within the quarter 17/10/2008 26/08/2008 Pull in 30/05/2008 26/08/2008 Push out of quarter 29/08/2008 26/09/2008 Within the quarter 26/08/2008 07/01/2008 Pull in 29/08/2008 09/11/2008 Push out of quarter 08/11/2008 29/08/2008 Pull in 18/08/2008 24/08/2008 Within the quarter 07/11/2008 18/08/2008 Pull in 24/09/2008 26/09/2008 Within the quarter 07/11/2008 26/08/2008 Pull in 28/08/2008 24/09/2008 Within the quarter 27/08/2008 24/09/2008 Within the quarter 29/08/2008 19/09/2008 Within the quarter 18/08/2008 08/05/2008 Pull in 22/09/2008 30/09/2008 Within the quarter 09/01/2008 09/04/2008 Push out of quarter 30/09/2008 19/09/2008 Within the quarter 30/09/2008 09/04/2008 Pull in Are you sure that those are real dates? -- __________________________________ HTH Bob "nikko" wrote in message ... Bob, i copied the formula you provided earlier and the result is #value! except for one item ... it shows push out of quarter but when i take a further look; the old n new values are within the quarter (same month) .. Old Value New Value Formula 28/11/2008 08/09/2008 #VALUE! 30/09/2008 28/11/2008 #VALUE! 28/11/2008 18/08/2008 #VALUE! 22/08/2008 29/09/2008 #VALUE! 22/12/2008 18/08/2008 #VALUE! 30/07/2008 18/08/2008 #VALUE! 24/11/2008 30/09/2008 #VALUE! 30/09/2008 24/11/2008 #VALUE! 29/08/2008 30/09/2008 #VALUE! 18/07/2008 18/08/2008 #VALUE! 28/11/2008 18/08/2008 #VALUE! 27/09/2008 19/08/2008 #VALUE! 29/08/2008 19/09/2008 #VALUE! 17/10/2008 26/08/2008 #VALUE! 30/05/2008 26/08/2008 #VALUE! 29/08/2008 26/09/2008 #VALUE! 26/08/2008 01/07/2008 #VALUE! 29/08/2008 11/09/2008 #VALUE! 11/08/2008 29/08/2008 #VALUE! 18/08/2008 24/08/2008 #VALUE! 11/07/2008 18/08/2008 #VALUE! 24/09/2008 26/09/2008 #VALUE! 11/07/2008 26/08/2008 #VALUE! 28/08/2008 24/09/2008 #VALUE! 27/08/2008 24/09/2008 #VALUE! 29/08/2008 19/09/2008 #VALUE! 18/08/2008 05/08/2008 #VALUE! 22/09/2008 30/09/2008 #VALUE! 01/09/2008 04/09/2008 Push out of quarter 30/09/2008 19/09/2008 #VALUE! 30/09/2008 04/09/2008 #VALUE! -- nikko "Bob Phillips" wrote: =IF(YEAR(A2)*10+INT((MONTH(A2)+2)/3)=YEAR(B2)*10+INT((MONTH(B2)+2)/3),"Within the quarter", IF(YEAR(A2)*10+INT((MONTH(A2)+2)/3)YEAR(B2)*10+INT((MONTH(B2)+2)/3),"Pull in","Push out of quarter")) -- __________________________________ HTH Bob "nikko" wrote in message ... Hi I have a tracking spreadsheet that contains all the deals with the close date change.. there's a close date old value in column A and a close date new value in column B is there a formula where i can use to determine if the change of date is within the quarter; pull in or push out of quarter ? Column A Column B Formula 28-Nov-2008 12-Sep-2008 Pull in 28-Nov-2008 20-Oct-2008 within the quarter 28-Nov-2008 10-Jan-2009 Push out of quarter Any help is appreciated, Thanks! -- nikko |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
calculate difference between 2 dates
To see whether they are real dates, select the cells & use Format Cells to
change the format to another date option, for example from 28/11/2008 to 28 November 2008. If the values visible in the cells don't change, they are text, not real dates. -- David Biddulph "nikko" wrote in message ... bob, how do i ensure the dates are real dates? i have selected the columns, right-click and select format cells and ensure that the date category is selected but i still get the #value error.. -- nikko "Bob Phillips" wrote: I have just tried it with that data and I get Old Value New Value Formula 28/11/2008 08/09/2008 Pull in 30/09/2008 28/11/2008 Push out of quarter 28/11/2008 18/08/2008 Pull in 22/08/2008 29/09/2008 Within the quarter 22/12/2008 18/08/2008 Pull in 30/07/2008 18/08/2008 Within the quarter 24/11/2008 30/09/2008 Pull in 30/09/2008 24/11/2008 Push out of quarter 29/08/2008 30/09/2008 Within the quarter 18/07/2008 18/08/2008 Within the quarter 28/11/2008 18/08/2008 Pull in 27/09/2008 19/08/2008 Within the quarter 29/08/2008 19/09/2008 Within the quarter 17/10/2008 26/08/2008 Pull in 30/05/2008 26/08/2008 Push out of quarter 29/08/2008 26/09/2008 Within the quarter 26/08/2008 07/01/2008 Pull in 29/08/2008 09/11/2008 Push out of quarter 08/11/2008 29/08/2008 Pull in 18/08/2008 24/08/2008 Within the quarter 07/11/2008 18/08/2008 Pull in 24/09/2008 26/09/2008 Within the quarter 07/11/2008 26/08/2008 Pull in 28/08/2008 24/09/2008 Within the quarter 27/08/2008 24/09/2008 Within the quarter 29/08/2008 19/09/2008 Within the quarter 18/08/2008 08/05/2008 Pull in 22/09/2008 30/09/2008 Within the quarter 09/01/2008 09/04/2008 Push out of quarter 30/09/2008 19/09/2008 Within the quarter 30/09/2008 09/04/2008 Pull in Are you sure that those are real dates? -- __________________________________ HTH Bob "nikko" wrote in message ... Bob, i copied the formula you provided earlier and the result is #value! except for one item ... it shows push out of quarter but when i take a further look; the old n new values are within the quarter (same month) .. Old Value New Value Formula 28/11/2008 08/09/2008 #VALUE! 30/09/2008 28/11/2008 #VALUE! 28/11/2008 18/08/2008 #VALUE! 22/08/2008 29/09/2008 #VALUE! 22/12/2008 18/08/2008 #VALUE! 30/07/2008 18/08/2008 #VALUE! 24/11/2008 30/09/2008 #VALUE! 30/09/2008 24/11/2008 #VALUE! 29/08/2008 30/09/2008 #VALUE! 18/07/2008 18/08/2008 #VALUE! 28/11/2008 18/08/2008 #VALUE! 27/09/2008 19/08/2008 #VALUE! 29/08/2008 19/09/2008 #VALUE! 17/10/2008 26/08/2008 #VALUE! 30/05/2008 26/08/2008 #VALUE! 29/08/2008 26/09/2008 #VALUE! 26/08/2008 01/07/2008 #VALUE! 29/08/2008 11/09/2008 #VALUE! 11/08/2008 29/08/2008 #VALUE! 18/08/2008 24/08/2008 #VALUE! 11/07/2008 18/08/2008 #VALUE! 24/09/2008 26/09/2008 #VALUE! 11/07/2008 26/08/2008 #VALUE! 28/08/2008 24/09/2008 #VALUE! 27/08/2008 24/09/2008 #VALUE! 29/08/2008 19/09/2008 #VALUE! 18/08/2008 05/08/2008 #VALUE! 22/09/2008 30/09/2008 #VALUE! 01/09/2008 04/09/2008 Push out of quarter 30/09/2008 19/09/2008 #VALUE! 30/09/2008 04/09/2008 #VALUE! -- nikko "Bob Phillips" wrote: =IF(YEAR(A2)*10+INT((MONTH(A2)+2)/3)=YEAR(B2)*10+INT((MONTH(B2)+2)/3),"Within the quarter", IF(YEAR(A2)*10+INT((MONTH(A2)+2)/3)YEAR(B2)*10+INT((MONTH(B2)+2)/3),"Pull in","Push out of quarter")) -- __________________________________ HTH Bob "nikko" wrote in message ... Hi I have a tracking spreadsheet that contains all the deals with the close date change.. there's a close date old value in column A and a close date new value in column B is there a formula where i can use to determine if the change of date is within the quarter; pull in or push out of quarter ? Column A Column B Formula 28-Nov-2008 12-Sep-2008 Pull in 28-Nov-2008 20-Oct-2008 within the quarter 28-Nov-2008 10-Jan-2009 Push out of quarter Any help is appreciated, Thanks! -- nikko |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
calculate difference between 2 dates
It works for me even if the cells are text.
Can you post a sample workbook somewhere on one of the web share sites. -- __________________________________ HTH Bob "nikko" wrote in message ... bob, how do i ensure the dates are real dates? i have selected the columns, right-click and select format cells and ensure that the date category is selected but i still get the #value error.. -- nikko "Bob Phillips" wrote: I have just tried it with that data and I get Old Value New Value Formula 28/11/2008 08/09/2008 Pull in 30/09/2008 28/11/2008 Push out of quarter 28/11/2008 18/08/2008 Pull in 22/08/2008 29/09/2008 Within the quarter 22/12/2008 18/08/2008 Pull in 30/07/2008 18/08/2008 Within the quarter 24/11/2008 30/09/2008 Pull in 30/09/2008 24/11/2008 Push out of quarter 29/08/2008 30/09/2008 Within the quarter 18/07/2008 18/08/2008 Within the quarter 28/11/2008 18/08/2008 Pull in 27/09/2008 19/08/2008 Within the quarter 29/08/2008 19/09/2008 Within the quarter 17/10/2008 26/08/2008 Pull in 30/05/2008 26/08/2008 Push out of quarter 29/08/2008 26/09/2008 Within the quarter 26/08/2008 07/01/2008 Pull in 29/08/2008 09/11/2008 Push out of quarter 08/11/2008 29/08/2008 Pull in 18/08/2008 24/08/2008 Within the quarter 07/11/2008 18/08/2008 Pull in 24/09/2008 26/09/2008 Within the quarter 07/11/2008 26/08/2008 Pull in 28/08/2008 24/09/2008 Within the quarter 27/08/2008 24/09/2008 Within the quarter 29/08/2008 19/09/2008 Within the quarter 18/08/2008 08/05/2008 Pull in 22/09/2008 30/09/2008 Within the quarter 09/01/2008 09/04/2008 Push out of quarter 30/09/2008 19/09/2008 Within the quarter 30/09/2008 09/04/2008 Pull in Are you sure that those are real dates? -- __________________________________ HTH Bob "nikko" wrote in message ... Bob, i copied the formula you provided earlier and the result is #value! except for one item ... it shows push out of quarter but when i take a further look; the old n new values are within the quarter (same month) .. Old Value New Value Formula 28/11/2008 08/09/2008 #VALUE! 30/09/2008 28/11/2008 #VALUE! 28/11/2008 18/08/2008 #VALUE! 22/08/2008 29/09/2008 #VALUE! 22/12/2008 18/08/2008 #VALUE! 30/07/2008 18/08/2008 #VALUE! 24/11/2008 30/09/2008 #VALUE! 30/09/2008 24/11/2008 #VALUE! 29/08/2008 30/09/2008 #VALUE! 18/07/2008 18/08/2008 #VALUE! 28/11/2008 18/08/2008 #VALUE! 27/09/2008 19/08/2008 #VALUE! 29/08/2008 19/09/2008 #VALUE! 17/10/2008 26/08/2008 #VALUE! 30/05/2008 26/08/2008 #VALUE! 29/08/2008 26/09/2008 #VALUE! 26/08/2008 01/07/2008 #VALUE! 29/08/2008 11/09/2008 #VALUE! 11/08/2008 29/08/2008 #VALUE! 18/08/2008 24/08/2008 #VALUE! 11/07/2008 18/08/2008 #VALUE! 24/09/2008 26/09/2008 #VALUE! 11/07/2008 26/08/2008 #VALUE! 28/08/2008 24/09/2008 #VALUE! 27/08/2008 24/09/2008 #VALUE! 29/08/2008 19/09/2008 #VALUE! 18/08/2008 05/08/2008 #VALUE! 22/09/2008 30/09/2008 #VALUE! 01/09/2008 04/09/2008 Push out of quarter 30/09/2008 19/09/2008 #VALUE! 30/09/2008 04/09/2008 #VALUE! -- nikko "Bob Phillips" wrote: =IF(YEAR(A2)*10+INT((MONTH(A2)+2)/3)=YEAR(B2)*10+INT((MONTH(B2)+2)/3),"Within the quarter", IF(YEAR(A2)*10+INT((MONTH(A2)+2)/3)YEAR(B2)*10+INT((MONTH(B2)+2)/3),"Pull in","Push out of quarter")) -- __________________________________ HTH Bob "nikko" wrote in message ... Hi I have a tracking spreadsheet that contains all the deals with the close date change.. there's a close date old value in column A and a close date new value in column B is there a formula where i can use to determine if the change of date is within the quarter; pull in or push out of quarter ? Column A Column B Formula 28-Nov-2008 12-Sep-2008 Pull in 28-Nov-2008 20-Oct-2008 within the quarter 28-Nov-2008 10-Jan-2009 Push out of quarter Any help is appreciated, Thanks! -- nikko |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
calculate difference between 2 dates
=CHOOSE(1+(B1<DATE(YEAR(A1),MONTH(A1)-MOD(MONTH(A1)-1,3),1))
+2*(B1DATE(YEAR(A1),3+MONTH(A1)-MOD(MONTH(A1)-1,3),0)),"Within the quarter","Pull in","Push out of quarter") Regards, Bernd |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
calculate difference between 2 dates
I suspect the OP has leading spaces that way you would get value errors
-- Regards, Peo Sjoblom "Bob Phillips" wrote in message ... It works for me even if the cells are text. Can you post a sample workbook somewhere on one of the web share sites. -- __________________________________ HTH Bob "nikko" wrote in message ... bob, how do i ensure the dates are real dates? i have selected the columns, right-click and select format cells and ensure that the date category is selected but i still get the #value error.. -- nikko "Bob Phillips" wrote: I have just tried it with that data and I get Old Value New Value Formula 28/11/2008 08/09/2008 Pull in 30/09/2008 28/11/2008 Push out of quarter 28/11/2008 18/08/2008 Pull in 22/08/2008 29/09/2008 Within the quarter 22/12/2008 18/08/2008 Pull in 30/07/2008 18/08/2008 Within the quarter 24/11/2008 30/09/2008 Pull in 30/09/2008 24/11/2008 Push out of quarter 29/08/2008 30/09/2008 Within the quarter 18/07/2008 18/08/2008 Within the quarter 28/11/2008 18/08/2008 Pull in 27/09/2008 19/08/2008 Within the quarter 29/08/2008 19/09/2008 Within the quarter 17/10/2008 26/08/2008 Pull in 30/05/2008 26/08/2008 Push out of quarter 29/08/2008 26/09/2008 Within the quarter 26/08/2008 07/01/2008 Pull in 29/08/2008 09/11/2008 Push out of quarter 08/11/2008 29/08/2008 Pull in 18/08/2008 24/08/2008 Within the quarter 07/11/2008 18/08/2008 Pull in 24/09/2008 26/09/2008 Within the quarter 07/11/2008 26/08/2008 Pull in 28/08/2008 24/09/2008 Within the quarter 27/08/2008 24/09/2008 Within the quarter 29/08/2008 19/09/2008 Within the quarter 18/08/2008 08/05/2008 Pull in 22/09/2008 30/09/2008 Within the quarter 09/01/2008 09/04/2008 Push out of quarter 30/09/2008 19/09/2008 Within the quarter 30/09/2008 09/04/2008 Pull in Are you sure that those are real dates? -- __________________________________ HTH Bob "nikko" wrote in message ... Bob, i copied the formula you provided earlier and the result is #value! except for one item ... it shows push out of quarter but when i take a further look; the old n new values are within the quarter (same month) .. Old Value New Value Formula 28/11/2008 08/09/2008 #VALUE! 30/09/2008 28/11/2008 #VALUE! 28/11/2008 18/08/2008 #VALUE! 22/08/2008 29/09/2008 #VALUE! 22/12/2008 18/08/2008 #VALUE! 30/07/2008 18/08/2008 #VALUE! 24/11/2008 30/09/2008 #VALUE! 30/09/2008 24/11/2008 #VALUE! 29/08/2008 30/09/2008 #VALUE! 18/07/2008 18/08/2008 #VALUE! 28/11/2008 18/08/2008 #VALUE! 27/09/2008 19/08/2008 #VALUE! 29/08/2008 19/09/2008 #VALUE! 17/10/2008 26/08/2008 #VALUE! 30/05/2008 26/08/2008 #VALUE! 29/08/2008 26/09/2008 #VALUE! 26/08/2008 01/07/2008 #VALUE! 29/08/2008 11/09/2008 #VALUE! 11/08/2008 29/08/2008 #VALUE! 18/08/2008 24/08/2008 #VALUE! 11/07/2008 18/08/2008 #VALUE! 24/09/2008 26/09/2008 #VALUE! 11/07/2008 26/08/2008 #VALUE! 28/08/2008 24/09/2008 #VALUE! 27/08/2008 24/09/2008 #VALUE! 29/08/2008 19/09/2008 #VALUE! 18/08/2008 05/08/2008 #VALUE! 22/09/2008 30/09/2008 #VALUE! 01/09/2008 04/09/2008 Push out of quarter 30/09/2008 19/09/2008 #VALUE! 30/09/2008 04/09/2008 #VALUE! -- nikko "Bob Phillips" wrote: =IF(YEAR(A2)*10+INT((MONTH(A2)+2)/3)=YEAR(B2)*10+INT((MONTH(B2)+2)/3),"Within the quarter", IF(YEAR(A2)*10+INT((MONTH(A2)+2)/3)YEAR(B2)*10+INT((MONTH(B2)+2)/3),"Pull in","Push out of quarter")) -- __________________________________ HTH Bob "nikko" wrote in message ... Hi I have a tracking spreadsheet that contains all the deals with the close date change.. there's a close date old value in column A and a close date new value in column B is there a formula where i can use to determine if the change of date is within the quarter; pull in or push out of quarter ? Column A Column B Formula 28-Nov-2008 12-Sep-2008 Pull in 28-Nov-2008 20-Oct-2008 within the quarter 28-Nov-2008 10-Jan-2009 Push out of quarter Any help is appreciated, Thanks! -- nikko |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
calculate difference between 2 dates
Good thinking Mr Sjoblom!
nikko, try this attempt =IF(YEAR(TRIM(A2))*10+INT((MONTH(TRIM(A2))+2)/3)=YEAR(TRIM(B2))*10+INT((MONTH(TRIM(B2))+2)/3),"Within the quarter", IF(YEAR(TRIM(A2))*10+INT((MONTH(TRIM(A2))+2)/3)YEAR(TRIM(B2))*10+INT((MONTH(TRIM(B2))+2)/3),"Pull in","Push out of quarter")) -- __________________________________ HTH Bob "Peo Sjoblom" wrote in message ... I suspect the OP has leading spaces that way you would get value errors -- Regards, Peo Sjoblom "Bob Phillips" wrote in message ... It works for me even if the cells are text. Can you post a sample workbook somewhere on one of the web share sites. -- __________________________________ HTH Bob "nikko" wrote in message ... bob, how do i ensure the dates are real dates? i have selected the columns, right-click and select format cells and ensure that the date category is selected but i still get the #value error.. -- nikko |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
calculate difference between 2 dates
Peo, what's OP stand for and how can i go about rectifyin the leading spaces
you mentioned.. thanks -- nikko "Peo Sjoblom" wrote: I suspect the OP has leading spaces that way you would get value errors -- Regards, Peo Sjoblom "Bob Phillips" wrote in message ... It works for me even if the cells are text. Can you post a sample workbook somewhere on one of the web share sites. -- __________________________________ HTH Bob "nikko" wrote in message ... bob, how do i ensure the dates are real dates? i have selected the columns, right-click and select format cells and ensure that the date category is selected but i still get the #value error.. -- nikko "Bob Phillips" wrote: I have just tried it with that data and I get Old Value New Value Formula 28/11/2008 08/09/2008 Pull in 30/09/2008 28/11/2008 Push out of quarter 28/11/2008 18/08/2008 Pull in 22/08/2008 29/09/2008 Within the quarter 22/12/2008 18/08/2008 Pull in 30/07/2008 18/08/2008 Within the quarter 24/11/2008 30/09/2008 Pull in 30/09/2008 24/11/2008 Push out of quarter 29/08/2008 30/09/2008 Within the quarter 18/07/2008 18/08/2008 Within the quarter 28/11/2008 18/08/2008 Pull in 27/09/2008 19/08/2008 Within the quarter 29/08/2008 19/09/2008 Within the quarter 17/10/2008 26/08/2008 Pull in 30/05/2008 26/08/2008 Push out of quarter 29/08/2008 26/09/2008 Within the quarter 26/08/2008 07/01/2008 Pull in 29/08/2008 09/11/2008 Push out of quarter 08/11/2008 29/08/2008 Pull in 18/08/2008 24/08/2008 Within the quarter 07/11/2008 18/08/2008 Pull in 24/09/2008 26/09/2008 Within the quarter 07/11/2008 26/08/2008 Pull in 28/08/2008 24/09/2008 Within the quarter 27/08/2008 24/09/2008 Within the quarter 29/08/2008 19/09/2008 Within the quarter 18/08/2008 08/05/2008 Pull in 22/09/2008 30/09/2008 Within the quarter 09/01/2008 09/04/2008 Push out of quarter 30/09/2008 19/09/2008 Within the quarter 30/09/2008 09/04/2008 Pull in Are you sure that those are real dates? -- __________________________________ HTH Bob "nikko" wrote in message ... Bob, i copied the formula you provided earlier and the result is #value! except for one item ... it shows push out of quarter but when i take a further look; the old n new values are within the quarter (same month) .. Old Value New Value Formula 28/11/2008 08/09/2008 #VALUE! 30/09/2008 28/11/2008 #VALUE! 28/11/2008 18/08/2008 #VALUE! 22/08/2008 29/09/2008 #VALUE! 22/12/2008 18/08/2008 #VALUE! 30/07/2008 18/08/2008 #VALUE! 24/11/2008 30/09/2008 #VALUE! 30/09/2008 24/11/2008 #VALUE! 29/08/2008 30/09/2008 #VALUE! 18/07/2008 18/08/2008 #VALUE! 28/11/2008 18/08/2008 #VALUE! 27/09/2008 19/08/2008 #VALUE! 29/08/2008 19/09/2008 #VALUE! 17/10/2008 26/08/2008 #VALUE! 30/05/2008 26/08/2008 #VALUE! 29/08/2008 26/09/2008 #VALUE! 26/08/2008 01/07/2008 #VALUE! 29/08/2008 11/09/2008 #VALUE! 11/08/2008 29/08/2008 #VALUE! 18/08/2008 24/08/2008 #VALUE! 11/07/2008 18/08/2008 #VALUE! 24/09/2008 26/09/2008 #VALUE! 11/07/2008 26/08/2008 #VALUE! 28/08/2008 24/09/2008 #VALUE! 27/08/2008 24/09/2008 #VALUE! 29/08/2008 19/09/2008 #VALUE! 18/08/2008 05/08/2008 #VALUE! 22/09/2008 30/09/2008 #VALUE! 01/09/2008 04/09/2008 Push out of quarter 30/09/2008 19/09/2008 #VALUE! 30/09/2008 04/09/2008 #VALUE! -- nikko "Bob Phillips" wrote: =IF(YEAR(A2)*10+INT((MONTH(A2)+2)/3)=YEAR(B2)*10+INT((MONTH(B2)+2)/3),"Within the quarter", IF(YEAR(A2)*10+INT((MONTH(A2)+2)/3)YEAR(B2)*10+INT((MONTH(B2)+2)/3),"Pull in","Push out of quarter")) -- __________________________________ HTH Bob "nikko" wrote in message ... Hi I have a tracking spreadsheet that contains all the deals with the close date change.. there's a close date old value in column A and a close date new value in column B is there a formula where i can use to determine if the change of date is within the quarter; pull in or push out of quarter ? Column A Column B Formula 28-Nov-2008 12-Sep-2008 Pull in 28-Nov-2008 20-Oct-2008 within the quarter 28-Nov-2008 10-Jan-2009 Push out of quarter Any help is appreciated, Thanks! -- nikko |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
calculate difference between 2 dates
See Bob's latest post
OP stands for Original Poster. I swear, it's no insult or anything :) -- Regards, Peo Sjoblom "nikko" wrote in message ... Peo, what's OP stand for and how can i go about rectifyin the leading spaces you mentioned.. thanks -- nikko "Peo Sjoblom" wrote: I suspect the OP has leading spaces that way you would get value errors -- Regards, Peo Sjoblom "Bob Phillips" wrote in message ... It works for me even if the cells are text. Can you post a sample workbook somewhere on one of the web share sites. -- __________________________________ HTH Bob "nikko" wrote in message ... bob, how do i ensure the dates are real dates? i have selected the columns, right-click and select format cells and ensure that the date category is selected but i still get the #value error.. -- nikko "Bob Phillips" wrote: I have just tried it with that data and I get Old Value New Value Formula 28/11/2008 08/09/2008 Pull in 30/09/2008 28/11/2008 Push out of quarter 28/11/2008 18/08/2008 Pull in 22/08/2008 29/09/2008 Within the quarter 22/12/2008 18/08/2008 Pull in 30/07/2008 18/08/2008 Within the quarter 24/11/2008 30/09/2008 Pull in 30/09/2008 24/11/2008 Push out of quarter 29/08/2008 30/09/2008 Within the quarter 18/07/2008 18/08/2008 Within the quarter 28/11/2008 18/08/2008 Pull in 27/09/2008 19/08/2008 Within the quarter 29/08/2008 19/09/2008 Within the quarter 17/10/2008 26/08/2008 Pull in 30/05/2008 26/08/2008 Push out of quarter 29/08/2008 26/09/2008 Within the quarter 26/08/2008 07/01/2008 Pull in 29/08/2008 09/11/2008 Push out of quarter 08/11/2008 29/08/2008 Pull in 18/08/2008 24/08/2008 Within the quarter 07/11/2008 18/08/2008 Pull in 24/09/2008 26/09/2008 Within the quarter 07/11/2008 26/08/2008 Pull in 28/08/2008 24/09/2008 Within the quarter 27/08/2008 24/09/2008 Within the quarter 29/08/2008 19/09/2008 Within the quarter 18/08/2008 08/05/2008 Pull in 22/09/2008 30/09/2008 Within the quarter 09/01/2008 09/04/2008 Push out of quarter 30/09/2008 19/09/2008 Within the quarter 30/09/2008 09/04/2008 Pull in Are you sure that those are real dates? -- __________________________________ HTH Bob "nikko" wrote in message ... Bob, i copied the formula you provided earlier and the result is #value! except for one item ... it shows push out of quarter but when i take a further look; the old n new values are within the quarter (same month) .. Old Value New Value Formula 28/11/2008 08/09/2008 #VALUE! 30/09/2008 28/11/2008 #VALUE! 28/11/2008 18/08/2008 #VALUE! 22/08/2008 29/09/2008 #VALUE! 22/12/2008 18/08/2008 #VALUE! 30/07/2008 18/08/2008 #VALUE! 24/11/2008 30/09/2008 #VALUE! 30/09/2008 24/11/2008 #VALUE! 29/08/2008 30/09/2008 #VALUE! 18/07/2008 18/08/2008 #VALUE! 28/11/2008 18/08/2008 #VALUE! 27/09/2008 19/08/2008 #VALUE! 29/08/2008 19/09/2008 #VALUE! 17/10/2008 26/08/2008 #VALUE! 30/05/2008 26/08/2008 #VALUE! 29/08/2008 26/09/2008 #VALUE! 26/08/2008 01/07/2008 #VALUE! 29/08/2008 11/09/2008 #VALUE! 11/08/2008 29/08/2008 #VALUE! 18/08/2008 24/08/2008 #VALUE! 11/07/2008 18/08/2008 #VALUE! 24/09/2008 26/09/2008 #VALUE! 11/07/2008 26/08/2008 #VALUE! 28/08/2008 24/09/2008 #VALUE! 27/08/2008 24/09/2008 #VALUE! 29/08/2008 19/09/2008 #VALUE! 18/08/2008 05/08/2008 #VALUE! 22/09/2008 30/09/2008 #VALUE! 01/09/2008 04/09/2008 Push out of quarter 30/09/2008 19/09/2008 #VALUE! 30/09/2008 04/09/2008 #VALUE! -- nikko "Bob Phillips" wrote: =IF(YEAR(A2)*10+INT((MONTH(A2)+2)/3)=YEAR(B2)*10+INT((MONTH(B2)+2)/3),"Within the quarter", IF(YEAR(A2)*10+INT((MONTH(A2)+2)/3)YEAR(B2)*10+INT((MONTH(B2)+2)/3),"Pull in","Push out of quarter")) -- __________________________________ HTH Bob "nikko" wrote in message ... Hi I have a tracking spreadsheet that contains all the deals with the close date change.. there's a close date old value in column A and a close date new value in column B is there a formula where i can use to determine if the change of date is within the quarter; pull in or push out of quarter ? Column A Column B Formula 28-Nov-2008 12-Sep-2008 Pull in 28-Nov-2008 20-Oct-2008 within the quarter 28-Nov-2008 10-Jan-2009 Push out of quarter Any help is appreciated, Thanks! -- nikko |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
calculate difference between 2 dates
bob, i'm still facing the same error ..
where can i go to post you a sample of the workbook ... -- nikko "Bob Phillips" wrote: Good thinking Mr Sjoblom! nikko, try this attempt =IF(YEAR(TRIM(A2))*10+INT((MONTH(TRIM(A2))+2)/3)=YEAR(TRIM(B2))*10+INT((MONTH(TRIM(B2))+2)/3),"Within the quarter", IF(YEAR(TRIM(A2))*10+INT((MONTH(TRIM(A2))+2)/3)YEAR(TRIM(B2))*10+INT((MONTH(TRIM(B2))+2)/3),"Pull in","Push out of quarter")) -- __________________________________ HTH Bob "Peo Sjoblom" wrote in message ... I suspect the OP has leading spaces that way you would get value errors -- Regards, Peo Sjoblom "Bob Phillips" wrote in message ... It works for me even if the cells are text. Can you post a sample workbook somewhere on one of the web share sites. -- __________________________________ HTH Bob "nikko" wrote in message ... bob, how do i ensure the dates are real dates? i have selected the columns, right-click and select format cells and ensure that the date category is selected but i still get the #value error.. -- nikko |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
calculate difference between 2 dates
hello all! the formula is workin now..
Error is due to the different date format set in my comp .. Thank you!! -- nikko "nikko" wrote: bob, i'm still facing the same error .. where can i go to post you a sample of the workbook ... -- nikko "Bob Phillips" wrote: Good thinking Mr Sjoblom! nikko, try this attempt =IF(YEAR(TRIM(A2))*10+INT((MONTH(TRIM(A2))+2)/3)=YEAR(TRIM(B2))*10+INT((MONTH(TRIM(B2))+2)/3),"Within the quarter", IF(YEAR(TRIM(A2))*10+INT((MONTH(TRIM(A2))+2)/3)YEAR(TRIM(B2))*10+INT((MONTH(TRIM(B2))+2)/3),"Pull in","Push out of quarter")) -- __________________________________ HTH Bob "Peo Sjoblom" wrote in message ... I suspect the OP has leading spaces that way you would get value errors -- Regards, Peo Sjoblom "Bob Phillips" wrote in message ... It works for me even if the cells are text. Can you post a sample workbook somewhere on one of the web share sites. -- __________________________________ HTH Bob "nikko" wrote in message ... bob, how do i ensure the dates are real dates? i have selected the columns, right-click and select format cells and ensure that the date category is selected but i still get the #value error.. -- nikko |
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
calculate difference between 2 dates
Can you explain that nikko, it is not clear to me.
-- __________________________________ HTH Bob "nikko" wrote in message ... hello all! the formula is workin now.. Error is due to the different date format set in my comp .. Thank you!! -- nikko "nikko" wrote: bob, i'm still facing the same error .. where can i go to post you a sample of the workbook ... -- nikko "Bob Phillips" wrote: Good thinking Mr Sjoblom! nikko, try this attempt =IF(YEAR(TRIM(A2))*10+INT((MONTH(TRIM(A2))+2)/3)=YEAR(TRIM(B2))*10+INT((MONTH(TRIM(B2))+2)/3),"Within the quarter", IF(YEAR(TRIM(A2))*10+INT((MONTH(TRIM(A2))+2)/3)YEAR(TRIM(B2))*10+INT((MONTH(TRIM(B2))+2)/3),"Pull in","Push out of quarter")) -- __________________________________ HTH Bob "Peo Sjoblom" wrote in message ... I suspect the OP has leading spaces that way you would get value errors -- Regards, Peo Sjoblom "Bob Phillips" wrote in message ... It works for me even if the cells are text. Can you post a sample workbook somewhere on one of the web share sites. -- __________________________________ HTH Bob "nikko" wrote in message ... bob, how do i ensure the dates are real dates? i have selected the columns, right-click and select format cells and ensure that the date category is selected but i still get the #value error.. -- nikko |
|
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
calculate difference between 2 dates
my computer date format is MM-DD-YYYY whereas the excel dates are in
DD-MM-YYYY ... tt's why below example is showing push out of quarter even though both dates are in sept... 01/09/2008 04/09/2008 Push out of quarter when i update my computer settings, all the line items worked.... :) -- nikko "Bob Phillips" wrote: Can you explain that nikko, it is not clear to me. -- __________________________________ HTH Bob "nikko" wrote in message ... hello all! the formula is workin now.. Error is due to the different date format set in my comp .. Thank you!! -- nikko "nikko" wrote: bob, i'm still facing the same error .. where can i go to post you a sample of the workbook ... -- nikko "Bob Phillips" wrote: Good thinking Mr Sjoblom! nikko, try this attempt =IF(YEAR(TRIM(A2))*10+INT((MONTH(TRIM(A2))+2)/3)=YEAR(TRIM(B2))*10+INT((MONTH(TRIM(B2))+2)/3),"Within the quarter", IF(YEAR(TRIM(A2))*10+INT((MONTH(TRIM(A2))+2)/3)YEAR(TRIM(B2))*10+INT((MONTH(TRIM(B2))+2)/3),"Pull in","Push out of quarter")) -- __________________________________ HTH Bob "Peo Sjoblom" wrote in message ... I suspect the OP has leading spaces that way you would get value errors -- Regards, Peo Sjoblom "Bob Phillips" wrote in message ... It works for me even if the cells are text. Can you post a sample workbook somewhere on one of the web share sites. -- __________________________________ HTH Bob "nikko" wrote in message ... bob, how do i ensure the dates are real dates? i have selected the columns, right-click and select format cells and ensure that the date category is selected but i still get the #value error.. -- nikko |
|
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
calculate difference between 2 dates
Odd, Excel should inherit your computer settings, so it shouldn't matter.
-- __________________________________ HTH Bob "nikko" wrote in message ... my computer date format is MM-DD-YYYY whereas the excel dates are in DD-MM-YYYY ... tt's why below example is showing push out of quarter even though both dates are in sept... 01/09/2008 04/09/2008 Push out of quarter when i update my computer settings, all the line items worked.... :) -- nikko "Bob Phillips" wrote: Can you explain that nikko, it is not clear to me. -- __________________________________ HTH Bob "nikko" wrote in message ... hello all! the formula is workin now.. Error is due to the different date format set in my comp .. Thank you!! -- nikko "nikko" wrote: bob, i'm still facing the same error .. where can i go to post you a sample of the workbook ... -- nikko "Bob Phillips" wrote: Good thinking Mr Sjoblom! nikko, try this attempt =IF(YEAR(TRIM(A2))*10+INT((MONTH(TRIM(A2))+2)/3)=YEAR(TRIM(B2))*10+INT((MONTH(TRIM(B2))+2)/3),"Within the quarter", IF(YEAR(TRIM(A2))*10+INT((MONTH(TRIM(A2))+2)/3)YEAR(TRIM(B2))*10+INT((MONTH(TRIM(B2))+2)/3),"Pull in","Push out of quarter")) -- __________________________________ HTH Bob "Peo Sjoblom" wrote in message ... I suspect the OP has leading spaces that way you would get value errors -- Regards, Peo Sjoblom "Bob Phillips" wrote in message ... It works for me even if the cells are text. Can you post a sample workbook somewhere on one of the web share sites. -- __________________________________ HTH Bob "nikko" wrote in message ... bob, how do i ensure the dates are real dates? i have selected the columns, right-click and select format cells and ensure that the date category is selected but i still get the #value error.. -- nikko |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Calculate difference in dates | Excel Discussion (Misc queries) | |||
| How do I calculate exact difference between two dates in y,m,d. | Excel Worksheet Functions | |||
| How do I calculate the difference between 2 dates (m,d,y) ? | Excel Worksheet Functions | |||
| Calculate Difference b/e 2 dates. | New Users to Excel | |||
| Calculate difference between two dates | Excel Worksheet Functions | |||
Linear Mode
