No matter what I do Excel formula returns a date as 1905?

[Event2020]

Hi and Happy New Year.

I have a formula that is getting a list of dates from a separate spreadsheet - for this example let's call the external spreadsheet 'Sheet1.xls and the cell is A1. A1 is formatted as General.

Some dates maybe just the year "yyyy", eg, 2018 0r 2017 and so on - and other dates maybe "dd/mm/yyyy" eg, 04/01/2018 or 21/06/1992 and so on and the formula should look at the date and return it exactly as it is written in the spreadsheet it is pulling from.

I should say that I am in the UK and our date format is dd/mm/yyyy.

So the formula should return 2018 if only the year is in the other spreadsheet or it should return 04/01/2018 if that is what is in the other spreadsheet but it is returning the date as 1905.

I have been working on this all evening and I have tried so many of the various solutions offered on forums and web pages but none work, I feel I am just going around in circles and getting nowhere.

I have not come across this problem before and there must be a formula that returns exactly what is entered in a specific cell without trying to alter it in any way?

Any help or suggestions would be greatly welcome and appreciated.

 

[JoeMo]

Can you show us the formula that returns 1905, and the value(s) of the precedent cell(s) in that formula when it returns 1905?

 

[Sunny Kow]

Hi Event2020

Dates are numbers in Excel. If the cell contains a year such as 2017 or 2018 etc it will be interpreted as 09/07/1905 and 10/07/1905 respectively.
That is why you are getting 1905 as the year. Since your source cells contains either a year or an actual date you can try using this:
=IF(CELL("format",A1)="D1",TEXT(A1,"dd/mm/yyyy"),A1).
Change A1 to your actual cell. What this does is to check A1 to see if it is formatted as a number or a date and convert accordingly.
if CELL() returns "G" means number while "D1" represents a Date.
Hope this helps.

Sunny

 

[Event2020]

Hi and many thanks to JoeMo & Sunny Kow.

I have just got home from work and I will try Sunny Kow's suggestion
and report back later on this evening.

Many thanks.

 

[Sunny Kow]

Hi
Just in case your date is in different format, you can use this (more robust)
=IF(LEFT(CELL("format",A1),1)="D",TEXT(A1,"dd/mm/yyyy"),A1)

 

[Event2020]

Thanks to you both for your reply's.

Sunny Kow - your suggestion was spot on and did exactly what I needed.

Thanks again

kind regards

Event2020