calculate difference between 2 dates

  • Thread starter Thread starter nikko
  • Start date Start date
N

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!
 
=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"))
 
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!
 
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?
 
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..
 
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.
 
It works for me even if the cells are text.

Can you post a sample workbook somewhere on one of the web share sites.
 
=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
 
I suspect the OP has leading spaces that way you would get value errors

--


Regards,


Peo Sjoblom
 
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"))
 
Peo, what's OP stand for and how can i go about rectifyin the leading spaces
you mentioned..

thanks
 
See Bob's latest post

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


--


Regards,


Peo Sjoblom
 
bob, i'm still facing the same error ..
where can i go to post you a sample of the workbook ...
 
hello all! the formula is workin now..

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

Thank you!!
 
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.... :)
 
Back
Top