Date from timestamp string

[bertilak]

I have a device for reading blood pressure. For each reading it reports (along with blood pressure numbers) a timestamp in the format:

Jun 16 2019 06:01:44​

I want to extract just the date part (Jun 16 2019) which I will then convert to an excel date number (e.g. 43626).

How can I do this? Can it be done without VBA? I have a VBA solution but it causes Excel to ask me if I want to save changes every time I exit since it re-does that calculation every time even if nothing has changed. (At least I think that's why.)

 

[Excelnewie]

Hi
can you not just change the format to show date only, not date and time?

 

[Rick Rothstein]

Give this formula a try...

=DATEVALUE(SUBSTITUTE(A1," ",", ",2))

Note: I was not sure from your single example whether dates with single digit day numbers are shown with or without leading zeros. The above formula works no matter what, but if your day numbers will always have a leading zero, you could use this formula which I think may be more efficient...

=DATEVALUE(REPLACE(A1,7,0,", "))

 

[bertilak]

=DATEVALUE(SUBSTITUTE(A1," ",", ",2))

Works great!

Thanks