Perplexing DATE calculations problem

Please be patient, I am still relatively new to VBA and have now hit Date problems.

I have a sheet of burial records, one column contains the death dates, many of which are post 1900 (13/03/1961), but a lot are pre 1900 (28 December 1874). Another column contains the age at death in various formats which in all cases I have been able to convert to a count of DAYS.

For output to a file for another program I need to calculate the birth date, i.e Death Date - age in days, as the majority of these will become pre 1900 dates this would be preferrable in Text date format e.g 15 February 1886.

My first thinking was to temporarily increase the century on all dates,
century = Val(Mid(deathdate, (Len(deathdate)-3),2))+1 subtract the age in days using Excel, decrease the century again by the same amount and construct a Text date.

My first problem was getting the date calculation to work,

birth = Val(Cells(r, 3)) - Val(cells(r,4)) does not work
where column 3 is 17/06/1978 and column 4 is 25915 which should give me 05/07/1907 but doesn't, without the VAL I still have the same problem.

All help and suggestions on the best way tp proceed gratfully received.

Thx in advance

Originally Posted by XeRo Solus

Why do you have the age in days? Wouldn't mm/dd/yy be better so it could be a date subtraction?

Ignorance I would think, I thought that as Excel uses a decimal for the date starting 30 Dec 1899 that would be the best base. The original age at death contained various formats like "71", "10 Months", "18 Days", "1 6/12 Years" etc so I thought I would calculate days as a common base, I can easily re-do it.

Looks like this was not the right approach, so what would you suggest?

And what do you think of my increasing and decreasing the century idea to help with pre 1900 date results?

Thx

Originally Posted by shg

I think it's a fine idea. Or you could use an add-in: http://j-walk.com/ss/excel/files/xdate.htm

Thanks, that could be useful to my needs