Date format coverntion

Ned Ludd

Board Regular
Joined
May 13, 2004
Messages
56
I hope someone can help.

I have a user that typed in hundreds of dates in the following format:

MM/DD/YYYY

but her computer is set up to recognize dates as YYYY/MM/DD and as such excel does not recognize the info she has put in as dates so that she can use the networkdays function.

I have tried using the data - text to collums format to seperate the 3 numbers and then recombining them in the right order using this formula:

=a1&"/"&b1

but excel still does not recognize the newly combined numbers as a date.

What praytell am I doing wrong?

Thank you very much for your thoughts.


Ned.
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,913
Office Version
  1. 365
Platform
  1. Windows
Ned

Why not use Data>Text to columns... and on the third step select whatever date format has been used from the dropdown?

That should convert the dates into true dates which can be formatted as required.

By the way are you sure they aren't being recognised as dates already, but are just formatted incorrectly.
 

Ned Ludd

Board Regular
Joined
May 13, 2004
Messages
56
Thank you very much for your reply Norie. I could not get this to work and have just asked the user to retype in the dates in the proper format.

And my reputation as an excel guru has been shattered. :oops:

Ned.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,913
Office Version
  1. 365
Platform
  1. Windows
Ned

What exactly did you try?

What date format did you select on the 3rd step?
 

Watch MrExcel Video

Forum statistics

Threads
1,113,850
Messages
5,544,650
Members
410,627
Latest member
georgealice
Top