Hi there,
I have a data that shows date/time data. Currently, I couldn't transform the date/time format into a specific one due to unknown reasons.
As you can see above, some of the date/time are in different formats. Whenever I tried to change the date/time format through TEXT Formula or through cells formatting, it doesn't seem to work well.
The date/time format should be in "MM/DD/YYYY" which only works on certain cells.
Are there any ways I can further change the date/time format here? Thank you very much!
Formatting Date/Time to a Specific Format
-
Suggest you upload your file or a sample of your file so that it can be investigated. We cannot manipulate date from a picture nor can we determine if the dates are true dates or text. We will need to see a representative file to analyze if properly.
-
Ok. Your issue is that some of the items in your list are not Dates, but Text that look like dates. Dates are really numbers that represent the number of days since January 1, 1900 that are formatted to look like dates. To test which ones are not dates use this formula in an adjacent cell =IsNumber(Cell address) and it will return a True or False.
To convert those text values to true dates, in a helper cell type = cellrange *1, ie. =B15 * 1
-
Hi alansidman
Thank you so much! Your explanation is correct and I understand it well. However, I keep getting these errors to some of those date/time.
Does this mean I can't change the date/time for those in errors?
Thank you so much! -
-
See attached. I have no issue changing them when I multiply them by 1
-
The formats seem to be jumbled up.
To apply a Date or Time format, execute the following steps.
- Select the required cell.
- Right-click, and then click Format Cells.
- In the Category list, select Date, and select a date format.
- Click OK.
- Dates are stored as numbers in Excel and count the number of days. Times are handled internally as numbers between 0 and 1.
- You can enter times as 6:00, but Excel displays this time as 6:00:00 AM in the formula bar. AM is used for times in the night and morning. PM is used for times in the afternoon and evening.
- Change the number format of the cell to Date only.
- Finally, if you cannot find the right date or time format, create a custom date or time format.
Hope this helps.
Regards,
Jerry M.
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!