Excel not recognizing date and time as number
Our take
Solution Verified: The issue was my windows regional settings. It was set to UK time and these date formats below are in US. I changed my region settings, restarted excel, and excel recognized it as numbers once I changed the format of the cells to date.
I have to calculate elapsed time between two dates and time.
For example:
Time elapsed between 1/20/2026 13:53 and 2/4/2026 11:29
In this case it is m/d/yyyy hh:mm
I need to know elapsed time in days, hours, and minutes
I wasn't able to export these dates/times from the system we use, so I had to copy and paste them into excel. As a result, Excel is unable to recognize the date/time format as a number and I cannot do any calculations on it.
I have tried the following:
- I tried the convert text to column feature and choose delimited on the whole column, deselect delimiters, and set the format to date (MDY). However when I do finish it doesn't do anything. It only works if I get rid of the time in the cell (for example if I just put 1/20/2026).
- Change the format to custom format (m/d/yyyy h:mm), but this doesn't seem to do anything either.
- Note if I go to Date and try to find the 3/14/12 1:30 PM format, it does not show in my list.
How can I get excel to recognize it as a number with both the day and time in the cell?
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- How to calculate elasped time in excel when date and time are in the same cell?I have series of data in the below format in excel. I want to calculate the elapsed time. I have tried multiple different way but nothing seems to be working. Format: MM/DD/YYYY hh:mm:ss Column: C4 - C2500 ____________________________________________ Formulas I have tried: Flash entery Separating time into a different column (column D) and calculating batch hours ( column E) with this formula =(D4 - $D$4)*24 (Note: problem with 2 option is, after 12 hours period the batch hour goes to negative instead of getting added continuously. I did check the column format for column D and it was set 'hh:mm:ss') Please help, I need to get this sorted soon 😭 Thank you submitted by /u/Shining_Swan [link] [comments]
- US dates entered suddenly being interpreted as European datesI have a worksheet I use to calculate my volunteer mileage for tax purposes, copying it each year to a new version for the current year. I enter in the date in the first column (mm/dd/yy) and it displays in my desired format (mm-dd-yyyy). Worked great for years... until today. I cleared out the data and started entering in dates, and I noticed some of the dates were changing to the correct format and others were staying in the entered format. Upon further investigation, I realized the entered dates were being interpreted as European dates (dd/mm/yy). I changed the formatting for the column to mmm-dd-yyyy and saw this plainly. I confirmed that the Windows regional attributes were correctly set for the US, including the date formats. (I've never touch this, but I wanted to be sure.) I went back and recopied the original worksheet to the new year's version, and I noticed all of the dates in both the old year's and new were now being shown in the dd-mm-yyyy format. When I entered in 1/6/25, and it displayed as Jun-01-2025. What the heck? I am baffled as to why this is suddenly happening for a worksheet that has been performing perfectly fine for several years. Thoughts? submitted by /u/melrowgo [link] [comments]
- Change the number format correctlyI accidentally entered the time as "3:04" using the "Number" format, with minutes first, followed by a period, and then seconds. If I change the number format now to "[h]:mm:ss", the numbers change because Excel recalculates the date. I’ve already entered 830 rows, and it would take hours to retype everything manually. Is there a macro or some kind of formula or rule I can use to change everything to the correct format at once? https://preview.redd.it/3pz12hha25zg1.png?width=1447&format=png&auto=webp&s=668213162de595aad5e1df968068f79a42aa9a06 submitted by /u/HugiCrafter_ [link] [comments]