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

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,303
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,303
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,140,941
Messages
5,703,301
Members
421,290
Latest member
james90

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