Forum Discussion
Using the LEFT Function on Date Formatted Cells
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.
