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.
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,353
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
76,353
Office Version
  1. 365
Platform
  1. Windows
Ned

What exactly did you try?

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

Forum statistics

Threads
1,176,369
Messages
5,902,740
Members
434,990
Latest member
Peter Jeffrey Gale

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top