Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Worksheet Functions > calculate difference between 2 dates
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 2,420
Default 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


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 30
Default 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



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 2,420
Default 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






Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 30
Default 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







Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 8,651
Default 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









Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 2,420
Default 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









Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 964
Default 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











Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 2,420
Default 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



Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 30
Default 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












Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Calculate difference in dates Geo Excel Discussion (Misc queries) 11 August 12th 08 07:18 AM
How do I calculate exact difference between two dates in y,m,d. Muhammad Javaid Hassan Excel Worksheet Functions 3 September 19th 06 01:42 PM
How do I calculate the difference between 2 dates (m,d,y) ? ady_sandu Excel Worksheet Functions 8 September 29th 05 05:09 PM
Calculate Difference b/e 2 dates. scharee New Users to Excel 2 August 2nd 05 04:28 PM
Calculate difference between two dates Trainer Excel Worksheet Functions 1 February 11th 05 02:04 PM


All times are GMT +1. The time now is 04:11 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement