SOLVED

Text Dates to Excel Date-Fields internal format

Copper Contributor

Mac OSX - Big Sur (11.6.1); Excel for Mac 16.55; Microsoft 365 Subscription; MacBook Pro 2019;

 

Have a column of log data that includes dates in the format:   Wednesday, Dec 08,2021 20:12:25

 

I'd like to preserve the date and time for pivot table work. DATEVALUE() results in #VALUE!

 

Merry Christmas, everyone - if you celebrate. Else, happy holidays.

4 Replies

@Peter_Kaufman Use Text-to-columns (Data ribbon) to split off the Day (skip), Date (MDY) and Time (General). Then you should end up with something like this.

Screenshot 2021-12-25 at 18.41.33.png

The steps in text-to-columns would look like this:

Screenshot 2021-12-25 at 18.35.00.pngScreenshot 2021-12-25 at 18.35.57.png

 

best response confirmed by Peter_Kaufman (Copper Contributor)
Solution

@Riny_van_EekelenThanks for your quick response!

 

However, I can't get that to work.  I tried removing all day-names and fixed format to set date using MDY and time into General format, but Excel still errors on #VALUE! for DateValue.

@Peter_Kaufman But the key is that, once you end up with two columns (a date and a time) you no longer need use DATEVALUE. One column is already a date and the other is a time. Though, I'm not sure what you are trying to achieve.

 

@Riny_van_Eekelen My misunderstanding.  Thank you very much for your help! -Peter

1 best response

Accepted Solutions
best response confirmed by Peter_Kaufman (Copper Contributor)
Solution

@Riny_van_EekelenThanks for your quick response!

 

However, I can't get that to work.  I tried removing all day-names and fixed format to set date using MDY and time into General format, but Excel still errors on #VALUE! for DateValue.

View solution in original post