Unable to extract timestamp from Date/Timestamp combo column in Excel 2016
I'm trying to extract a timestamp from a date/timestamp data combo into a different column, but I'm getting random numbers. I've tried text-to-columns, LEFT, and RIGHT. Then, I special-pasted the values thinking formatting might be messing with me. Nope. The values are still coming back wrong. For text-to-columns, I've delineated it with a space (ex: 2024-04-16 01:19:20) the original column maintains the date and then zeroes out the timestamp (00:00:00) instead of deleting it while the accurate timestamp is put into the new column. For LEFT and RIGHT functions, seemingly random numbers pop up when I try to isolate the string I want to transfer over. Even when I do the LEN and FIND functions, the length of characters is verifiably off and the FIND function returns an inaccurate position. The only thing I could think of is that the entered date is formatted slightly differently than what's shown on the spreadsheet, but a special paste didn't resolve the issue.
The reason functions like LEFT, RIGHT, LEN, and FIND are unreliable here is that Excel treats dates and timestamps as serial numbers under the hood. Excel stores dates and times as numeric values, where the integer portion represents the date and the decimal represents the time. When you try to use string-based functions like LEFT and RIGHT, Excel doesn’t interpret the date/timestamp as text but as a number, which is why you see random-looking (but not at all random as such) results. It is explained in detail on this page. For example:
To reliably extract parts of a date/timestamp, use Excel’s date and time functions (e.g., YEAR, MONTH, DAY, HOUR, MINUTE, SECOND). These functions work on the numeric serial format of the date and are designed to interpret it correctly, extracting each component accurately. For example:
=HOUR(A1) will give the hour from a timestamp in cell A1.
=MINUTE(A1) and =SECOND(A1) will give you the minute and second, respectively.
If you need the timestamp portion (e.g., 01:19:20) as a text string, format it as =TEXT(A1,"hh:mm:ss"). This approach uses Excel’s date system to interpret the values correctly, avoiding the issues you faced with string manipulation functions.
- Largest sum of consecutive values in row, but is it still consecutive in the cell of the last column?
- Dynamically Inserting a row and iterating over it in VBA?
- VBA class member function and class definition problem continues
- How can I perform COALESCE in power query?
- VBA class member function and class definition problem
- How can I set OptionButton font using VBA For Loop?
- VBA remove string custom salutation name from list table excel
- How to import many dataframes from R into a one Excel file in a simple way?
- How to add a missing column to the Transform Sample File
- Pull data from multiple workbooks based on a dynamic list
- Dependent dropdown list in Excel ,Data Validation and VBA
- Excel Formula: Filter Group based on Smallest Values
- Find column name of active cell within ListObject in Excel
- Strange characters found in XML file and PowerShell output after exporting from Excel: ​
- MID function inside COUNTIFS
- Finding Max of 3 Inputs VBA
- How to align duplicates on the same rows in Excel
- How to use .HTMLBody to hyperlink a cell value?
- Adding cell values, specific to each page, to each header
- Excel VBA macro for URL redirect detecting returns 200 instead of 301 sometimes
- Count with specific values in columns
- Convert IEEE Float hex string to an Excel VBA Single (float)
- Openpyxl or Pandas, which is better at reading data from a excel file and returning corresponding values
- Excel Function: Using a specific criteria to form an array to corresponding values
- How to pass metadata to Google Drive API upload with VBA
- IsError not capturing error returned by expression
- Excel VBA multi select drop down list
- Imported Excel file with dates, came out as numbers, trying to return to date format
- Creating Conditional Column based on another Table's Column
- Convert Range to a table