Forum Discussion
working with date/time
I have a file from a digital thermometer (used for grilling) that looks like this:
2/25/22, 3:00:00 PM 46
for 46 degrees at 3:00 PM on February 25, 2022.
I want to separate out the date (which I don't care about anyway) and the time, so I can compute the number of seconds between the readings.
If I convert the file to CSV and edit in Notepad, it looks like this:
"2/25/22, 3:00:00 PM" 46
I just want the 3:00:00 part.
WeldonWink1947 wrote:
If I convert the file to CSV and edit in Notepad, it looks like this:
"2/25/22, 3:00:00 PM" 46
That's odd. Is the date/time and the temp in the same cell or in two different cells?
When they are in the same cell, my CSV file contains the following when opened in Notepad:
"2/25/22, 3:00:00 PM 46"
When they are in two cells, Notepad shows:
"2/25/22, 3:00:00 PM",46
You seem to have misplaced either the right double-quote or a comma separator. Which is it?
Assuming the comma-separated date/time is in A1, enter the following into another cell:
=TIMEVALUE(MID(A1, 1+FIND("," , A1), 99))
formatted as Time in some form.
You can copy the formula result and paste-value-and-number-format into A1. Then you can delete the formula.
- I omitted the comma after 2nd quotation mark:
Here's a portion of the file, saved from Excel as CSV file:
Date & Time,Temperature (øF)
"2/25/22, 3:00:00 PM",46
"2/25/22, 3:57:10 PM",60
"2/25/22, 3:57:13 PM",60
"2/25/22, 3:57:16 PM",60
"2/25/22, 3:57:19 PM",60
"2/25/22, 3:57:35 PM",60
I've changed the format of column A to Time and Text, but neither seems to matter.
I'll try your suggestion- That worked! Thanks!
