SOLVED

=LEFT Function

Copper Contributor

Hey,

 

I'm a little troubled, where I seem to get this number from the left function instead of the date that is on the cell. Am I missing something? I've tried changing the number format to see if that would help but to no avail.2023-03-09.png

 

1 Reply
best response confirmed by Kevin_Curi (Copper Contributor)
Solution

@Kevin_Curi 

 

You might use the function LEFT(TEXT(D2,"m/d/yyyy hh:mm"),8).

 

But that is tautological.  You might as well use simply TEXT(D2,"m/d/yyyy"), with the understanding that that might be 8 to 10 characters, depending on the month and day numbers.

 

LEFT(D2,8) does not meet your expectation because Excel date (and time) is stored as a number, not the string that you see based on formatting.  Dates (or elapsed days) are the integer part, and time of day (or elapsed time) is the decimal fraction part, specifically the fraction of a (24-hour) day.

 

Usually (*), dates are stored as the number of days since 12/31/1899.  Thus, 1/1/1900 is the integer 1.

 

(*) The exception is when the 1904 date system option is set, the default on Macs, IIRC.  In that case, dates are stored as the number of days since 1/1/1904.  Thus, ,1/2/1904 (Jan 2) is the integer 1.

1 best response

Accepted Solutions
best response confirmed by Kevin_Curi (Copper Contributor)
Solution

@Kevin_Curi 

 

You might use the function LEFT(TEXT(D2,"m/d/yyyy hh:mm"),8).

 

But that is tautological.  You might as well use simply TEXT(D2,"m/d/yyyy"), with the understanding that that might be 8 to 10 characters, depending on the month and day numbers.

 

LEFT(D2,8) does not meet your expectation because Excel date (and time) is stored as a number, not the string that you see based on formatting.  Dates (or elapsed days) are the integer part, and time of day (or elapsed time) is the decimal fraction part, specifically the fraction of a (24-hour) day.

 

Usually (*), dates are stored as the number of days since 12/31/1899.  Thus, 1/1/1900 is the integer 1.

 

(*) The exception is when the 1904 date system option is set, the default on Macs, IIRC.  In that case, dates are stored as the number of days since 1/1/1904.  Thus, ,1/2/1904 (Jan 2) is the integer 1.

View solution in original post