having a problem with the date formula

[bigdan]

In col A I have the date and time together. For example:

1/05/2019 8:09:22 PM

In col B I wanted just the date, so I wrote a formula: =LEFT(A2,10)

I'm not getting "1/05/2019" the way I'd expect. Instead I'm getting 43470.8398

When I go to Short Date or Long Date I'm seeing the same result. If I go to Properties / Date it seems to be showing me day/month/year rather than the other way around. Dont know if that's related.

Can anyone advise?

 

[JackDanIce]

The display of the cell is NOT the same as the "value" of the cell.

Date and time are stored as numbers but displayed with date formats. I suspect LEFT(A1,10) displays the first 10 digits of the numerical value of the date and time.

You can either:

Edit the cell format in A1 (select range, right click, Cell Formats, then custom) to "DD/MM/YYYY" (or "MM/DD/YYYY" if you're of US preference) and apply to the range.

Or in B1 use

=TEXT(A1, "DD/MM/YYYY")

It's like "$1000" vs "1,000" vs "One Thousand" vs 1000 - they are all the same numerical value, but displayed with different formats; i.e. the "meaning" or context is interpreted by User but the actual number is still just a number.

 

[AlanY]

excel store time in fraction of a day, i.e. 24 hrs.
in your example, 43470 for the date and .8398 for time.

if you just wanted date without time can use =INT() to rid of the time and format the cell as date

 

[Nickaroo77]

Hi Big Dan,

You could try using the following:

=TEXT(LEFT(B2,10),"dd/mm/yy")

Hope this helps!