Using the LEFT Function on Date Formatted Cells

Copper Contributor

I have cells formatted to show the date as (ex. 1941-12-01), but when I use the LEFT function,
I am stumbling onto a issue where it is dividing the year into 12. It is spitting out 1531 when I put the function as: =LEFT(C2,4)


I have also noticed that Excel does not acknowledge dates before 1900? Is there a way that I can format a column to acknowledge dates before that time? I am building a pretty decent sized project.


Thank you!

3 Replies

@summertime0525 December 1, 1941 has an internal date value in Excel of 15311. That's why =LEFT(....,4) returned 1531.


If you want to return the year 1941 only from a date, use =YEAR(......)


Dates before 1900 are indeed not supported in Excel by default. Google for "dates before 1900 Excel" and you certainly find some clues about how to deal with it.



Thank you! I used =YEAR(.....) to cover all of the years after 1900. Then used =LEFT(......,4) to isolate any date before 1900 formatted as yyyy-mm-dd. After using =VALUE(.....) and then copy/pasting as values, I was able to isolate all years.

It took a while to manually retype the data, but since I manually inputted the data, a lesson was learned.


As variant


to use the same formula for both