rearranging dates

[revdjembo]

Hi,
I have inherited a spreadsheet in which has columns for two dates. A year column and a month and day column. The "year" column is simply yyyy so 1721. The "date" column has been written Mar-23, Aug-17, Dec-03 meaning 23rd March 1721, 17th August 1721, December 3rd 1721. Excel returns 01/03/2021 and so on for the "date" column. How can I convert/extract/undo(?) the date formatting so that I can extract the correct day and month from the "Month and day" column?
Any help much appreciated as I have from 1721 to 1981 to sort out!

excel year date.png

[Richard Buttrey]

Hi,

Excel doesn't hold date numbers prior to 1st January 1900, so you couldn't perform any data arithmetic in the normal way.
You could create strings of text that look like dates.

What are you trying to do. If it's the number of days/weeks..etc between two dates then you could start by adding say 200 years to both the start and end year number and then creating a proper date number.

However upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

To upload a file click the Go Advanced button at the foot of your post, look underneath the post area for the Manage Attachments section and take it from there.

[revdjembo]

Hi Richard,

thanks for taking the time to reply.

I've put some notes on the workbook. These are pages of a Burial Index I have inherited. As you will see the columns in blue are the original data as I get it. What I need is in the yellow column. In essence, the two blue columns joined up to give the correct date of the entry. So 2nd January 1832 instead of 1832 and 1st January 2002.

Thanks.

[Richard Buttrey]

Hi,

This uses a new helper column A with A2 = C2 and A3 copied down
=IF(C3="",A2,C3)



In new E2 copied down enter

=IF(AND($C1<>"Year",$C2<>""),"",RIGHT(YEAR($D2),2)&" "&TEXT(MONTH($D2)*28,"mmm")&" "&$A2)

See attached

[revdjembo]

Richard thank you so very much. I'm not sure I understand fully how it works - but it does and that's the important thing. This will save me so much time.

Thank you.

Jeremy