calculate difference between 2 dates

[nikko]

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!

 

[Bob Phillips]

=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"))

 

[nikko]

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!

 

[Bob Phillips]

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?

 

[nikko]

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..

 

[David Biddulph]

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.

 

[Bob Phillips]

It works for me even if the cells are text.

Can you post a sample workbook somewhere on one of the web share sites.

 

[Bernd P]

=CHOOSE(1+(B1<DATE(YEAR(A1),MONTH(A1)-MOD(MONTH(A1)-1,3),1))
+2*(B1>DATE(YEAR(A1),3+MONTH(A1)-MOD(MONTH(A1)-1,3),0)),"Within the
quarter","Pull in","Push out of quarter")

Regards,
Bernd

 

[Peo Sjoblom]

I suspect the OP has leading spaces that way you would get value errors

--


Regards,


Peo Sjoblom

 

[Bob Phillips]

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"))

 

[nikko]

Peo, what's OP stand for and how can i go about rectifyin the leading spaces
you mentioned..

thanks

 

[Peo Sjoblom]

See Bob's latest post

OP stands for Original Poster. I swear, it's no insult or anything


--


Regards,


Peo Sjoblom

 

[nikko]

bob, i'm still facing the same error ..
where can i go to post you a sample of the workbook ...

 

[nikko]

hello all! the formula is workin now..

Error is due to the different date format set in my comp ..

Thank you!!

 

[Bob Phillips]

Can you explain that nikko, it is not clear to me.

 

[nikko]

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....

 

[Bob Phillips]

Odd, Excel should inherit your computer settings, so it shouldn't matter.