Excel Dates

  brambles 21:11 18 Nov 05
Locked

Eldest daughter driving me mad.

At business she is working on a database in Excel.

She tries to shorten the DOB entry as for example
23/11/29 for someone born in 1929 but it comes out as 23/11/2029 yet when she enters 23/11/31 it comes out as she wants it as 23/11/1931. Is there something we need to format in Cells/Date?

Brambles

  v1asco 21:18 18 Nov 05

and have the same problem! Using excell 2000 and my cells are formatted correctly. Very strange, however there are lots of very helpful people on this site and here's hoping.

Taffy

  VoG II 21:35 18 Nov 05

Format the cells in which dates are added in the form DD/MM/YY as Text.

Then in another column enter the following formula and copy down

=DATE(RIGHT(A1,2)+1900,MID(A1,4,2),LEFT(A1,2))

(this assumes that the DD/MM/YY is in Column A starting in Row 1).

If you take this approach it is important to always enter the correct number of digits, e.g. 1 June 1949 would be entered as 01/06/49.

Having said all that, this is really only important if calculations are to be done on the dates - if not they could simply be formatted as Date - dd/mm/yy and would display correctly.

  brambles 17:58 19 Nov 05

Thank you bugle + VoG™ I'll pass the information on

Brambles

This thread is now locked and can not be replied to.

Best phone camera 2016/2017: Galaxy S7 vs iPhone 7 vs Google Pixel vs HTC 10 Evo vs OnePlus 3T vs…

1995-2015: How technology has changed the world in 20 years

Best Christmas Agency Projects of 2016

Super Mario Run preview | Hands-on first impressions of Super Mario Run: Mario's iPhone & iPad…