Date Difference but as Text Format from Form

[John_Mc]

Hi All,

I have two textboxes on a form (D.O.B1 & 2) which transfers the values to two cells. Because on transfer it updates it from 04/08/1950 (UK format) to 08/04/1950 (US format) the only solution i came up with was to ensure the cell was formated as text.

However, I now need to run the following calculation:

If DOB1 is blank, put zero here
If DOB 2 is blank, use DOB1
IF DOB1 < DOB2, use DOB1, otherwise use DOB2 (get oldest person)

My worksheet has:
L3 = DOB1 (formatted as date, but sourced using =Anotherworkbook cell that is formatted as text)

M3 = DOB2(formatted as date, but sourced using =Anotherworkbook cell that is formatted as text)

My formula is:
=IF(L3=0,"",IF(M3=0,((TODAY()-L3)/365),IF(L3<M3,((TODAY()-L3)/365),((TODAY()-M3)/365))))

But while L3 = 23/06/1915 and M3 = 01/04/1932, I am getting the answer false (when 1915 < 1932)

Can anyone help or point me in the right direction?

Cheers,
John Mc

[mdmackillop]

I'm not sure if this will help with your mixed formats.

From Excel Help
DateValue Function Example

This example uses the DateValue function to convert a string to a date. You can also use date literals to directly assign a date to a Variant or Date variable, for example, MyDate = #2/12/69#.
Dim MyDateMyDate = DateValue("February 12, 1969") ' Return a date.

[John_Mc]

Hi mdmackillop

Thanks for the pointer, i'll check the files and see what i come up with. Something else has come up for today, but i'll give it a try tomorrow and let you know.....

Cheers,
John Mc