Date Formula Query

[Quinton]

Hello can you help on this one?

I have 2 dates in 2 cells as shown below:

Column A, Line 1 08-03-11
Column B, Line 1 27-06-11

I need a formula to show the following answer in column 3, Line 1: 3

The "3" is shown because the date 27-06-11 happens in the "3rd" month after 08-03-11.

Is there a formula I can use that can do the above?

Cheers,

 

[e8stewie]

This should work for you.

=MONTH(A2)-MONTH(A1)

 

[T. Valko]

Hello can you help on this one?

I have 2 dates in 2 cells as shown below:

Column A, Line 1 08-03-11
Column B, Line 1 27-06-11

I need a formula to show the following answer in column 3, Line 1: 3

The "3" is shown because the date 27-06-11 happens in the "3rd" month after 08-03-11.

Is there a formula I can use that can do the above?

Cheers,

Maybe this...

=MONTH(B1)-MONTH(A1)

Format as General or Number

 

[Quinton]

Perfect as always!!!

Many thanks

 

[Quinton]

Aaaah spoke too soon - apologies!!

The formula worked perfectly: =MONTH(D63)-MONTH(F63)

However for some lines the result shows "#value!". This is because some of the data will show <empty> because the data is imported from somewhere else.

Can you please amend the formula to show "0" instead of "#value!"?

Again many thanks in advance!!

 

[number kruncher]

Wrap your formula with an IFERROR (excel 2007 on), eg.

=IFERROR(MONTH(D63)-MONTH(F63),0)

 

[T. Valko]

Aaaah spoke too soon - apologies!!

The formula worked perfectly: =MONTH(D63)-MONTH(F63)

However for some lines the result shows "#value!". This is because some of the data will show <EMPTY>because the data is imported from somewhere else.

Can you please amend the formula to show "0" instead of "#value!"?

Again many thanks in advance!!

This one will work in ANY version of Excel...

=IF(COUNT(D63,F3)<2,0,MONTH(D63)-MONTH(F63))

 

[Quinton]

Nearly there, apologies but I have 1 further formula request - as follows:

I have the following formula as show below:

=LOOKUP(2, 1/((Sdata!$A$1:$A$1000="Chestnut Grove\")*(Sdata!$B$1:$B$1000="Plot 02")),Sdata!$D$1:$D$1000)

Sometimes I get a result shown as "empty" <EMPTY>This is because the data found at that particular cell is "empty"<EMPTY>

When this happens can I get the result not to show "empty" <EMPTY>but to show "todays date"?

As always - Many, many thanks in advance!!!

Cheers,

 

[T. Valko]

Nearly there, apologies but I have 1 further formula request - as follows:

I have the following formula as show below:

=LOOKUP(2, 1/((Sdata!$A$1:$A$1000="Chestnut Grove\")*(Sdata!$B$1:$B$1000="Plot 02")),Sdata!$D$1:$D$1000)

Sometimes I get a result shown as "empty" <EMPTY>This is because the data found at that particular cell is "empty"<EMPTY>

When this happens can I get the result not to show "empty" <EMPTY>but to show "todays date"?

As always - Many, many thanks in advance!!!

Cheers,

What exactly does "shown as empty" mean?

If the last cell in the range that meets the conditions is empty then that formula will return 0.

Is that what it's doing?

 

[Quinton]

Apologies for poor explaination!

empty is actually written in the cell itself. When this happens I would like it to not display empty but to show todays date instead - is this possible?

Cheers,