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,

 

[T. Valko]

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,

Ok, well, without knowing what kind of values are in Sdata!$D$1:$D$1000, the generic method would be:

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

That's not very efficient since you have to perform the lookup function twice.

 

[Quinton]

Thanks very much for your kind response. The formula worked very well. However it now always shows todays date. I only want it to show todays date when the data only shows "<EMPTY>". I will give you an example of the data below:

Column A
14.05.07
empty
21.04.11
empty
19.10.11
<EMPTY>

Just to confirm the original formula I am using for all of the above is as follows:

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

I just need to change it so if the result is "empty<EMPTY>" then show todays date. If the result shows anything else (like the dates above) - then great.

Hope this helps?

Thanks again

 

[T. Valko]

Thanks very much for your kind response. The formula worked very well. However it now always shows todays date. I only want it to show todays date when the data only shows "<EMPTY>". I will give you an example of the data below:

Column A
14.05.07
empty
21.04.11
empty
19.10.11
<EMPTY>

Just to confirm the original formula I am using for all of the above is as follows:

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

I just need to change it so if the result is "empty<EMPTY>" then show todays date. If the result shows anything else (like the dates above) - then great.

Hope this helps?

Thanks again

The formula I suggested does what you asked for.

I don't understand your most recent request.

Your formula is looking for the last entry in the range data!$D$1:$D$1000 where Sdata!$A$1:$A$1000="Chestnut Grove\ and Sdata!$B$1:$B$1000="Plot 04".

With the formula I suggested, if the last entry in the range that meets both conditions is "Empty" then the result of the formula is today's data. If the entry is not "Empty" then the result is whatever entry is in the corresponding cell.

Can you post some sample data and tell us what result you expect?

 

[Quinton]

Many thanks for your response. Really sorry but I can't appear to copy,paste or use snipping tool etc. If you can explain how I can Post some data please let me know - alternatively I can email you the spreadsheet?

Below is a small example of some the data in the sheet "sdata"

------ Column A Column B Column D
Line 1 The Gables Plot 01 13/07/11
Line 2 The Gables Plot 02 14/08/10
Line 3 Heathlands Plot 01 empty (but with < before the e & > after the y)<EMPTY>
Line 4 Heathlands Plot 07 17/09/11
Line 5 Heathlands Plot 08 empty (but with < before the e & > after the y)<EMPTY><EMPTY>

Note: sorry but every time I type "empty" with the greater & smaller than signs Mr Excel won't publish it!


Just to recap what I am trying to do/achieve:

If Line 2 Column A = "The Gables" & Line 2 Column B = Plot 01 then display the data for Line 3 Column D. In this scenaio the result would be 14/08/10

However if Line 3 Column A = "Heathlands" & Line 3 Column B = Plot 01 then display the data for Line 3 Column D. In this scenaio the result would be "Empty" (with the greater & smaller than signs). If this is the case then do not show "Empty" - But show "todays Date".

If you understand that you deserve a medal!

Thanks again for your kind help.

 

[T. Valko]

Many thanks for your response. Really sorry but I can't appear to copy,paste or use snipping tool etc. If you can explain how I can Post some data please let me know - alternatively I can email you the spreadsheet?

Below is a small example of some the data in the sheet "sdata"

------ Column A Column B Column D
Line 1 The Gables Plot 01 13/07/11
Line 2 The Gables Plot 02 14/08/10
Line 3 Heathlands Plot 01 (<EMPTY>) without the brackets
Line 4 Heathlands Plot 07 17/09/11
Line 5 Heathlands Plot 08 (<EMPTY>) without the brackets

Note: sorry but every time I type "empty" with the greater & smaller than signs Mr Excel won't publish it! Therefore just take away the brackets & that is exactly what data I get.


Just to recap what I am trying to do/achieve:

If Line 2 Column A = "The Gables" & Line 2 Column B = Plot 01 then display the data for Line 3 Column D. In this scenaio the result would be 14/08/10

However if Line 3 Column A = "Heathlands" & Line 3 Column B = Plot 01 then display the data for Line 3 Column D. In this scenaio the result would be "Empty" (with the greater & smaller than signs). If this is the case then do not show "Empty" - But show "todays Date".

If you understand that you deserve a medal!

Thanks again for your kind help.

I'm not getting any medals today!

You can upload a sample file to some site and then post a link to that file so we can download it. If you don't have access to a site then there are lots of free file hosting sites out there.

 

[Quinton]

Sorry I just edited my previous response to you as MrExcel keeps changing what I type. Sorry but could you have 1 more look at my previous post?

Whilst you are doing that I will try a free file hosting site!

Thanks again

ps I think you deserver a medal!

 

[Quinton]

OK I think I have managed to Upload a sample file!!!

the 2 sheets in question are:

sdata
Where the data comes from

plot data
where I am inputting the formula (Lines 64-70, Column D)

http://www.mediafire.com/?0gvd76czvj5o4qx

If this works - I deserve a medal!

 

[T. Valko]

OK I think I have managed to Upload a sample file!!!

the 2 sheets in question are:

sdata
Where the data comes from

plot data
where I am inputting the formula (Lines 64-70, Column D)

http://www.mediafire.com/?0gvd76czvj5o4qx

If this works - I deserve a medal!

It worked!

Ok, I'm looking at your file...

It looks to me that on the the Sdata sheet, if you were to leave all the cells that contain < Empty > as empty cells or even numeric 0 then you could use a shorter more efficient formula to do what you want.

Is that something you could do, get rid of all those < Empty > entries?

Also, I would put today's date in some cell, like cell A1:

=TODAY()

Then refer to that cell rather than including the TODAY() function in every formula of which there are very many.

 

[Quinton]

Many thanks again (i'm dragging myself into the 21st century!)

Unfortunately I cant get rid of all the < empty > in the sheet "sdata". This is mainly because the data is exported from another software package & there are many lines.

Well being honest I could but it, would in some way, defeat the object of automating the report I will finally produce.

If you can still get a formula to work then please do. If not I guess we will have to manually take out all the < emty >.

Alternatively, could I not create another sheet that would mirror the data in "Sdata" but get it to change < empty > when it occurs to todays date. I would just then use that sheet to "get my data" ?

Good idea about putting todays date in A! (you have done this before!) & just referencing that cell.

Again many thanks for all your help!

 

[Quinton]

Don't worry as i've fixed it!

I created another sheet as described in my previous post!

Many thanks for your kind help!!!