Date change/format

jacques1

New Member
Joined
Aug 15, 2007
Messages
5
Hi All. Ist tru that the way the date are shown can be changed by the date format tool, provided the date was entered the correct way. I think we are missing each other. I think the way the date was entered (17.08.07) and converted to (17/08/07) was good. At first it was in text so one wouldnt have known whether it will be taken as yymmdd, ddmmyy, mmddyy etc. well it end up like as 17(yy), 08(mm), 07(dd) instead as 17(dd), 08(mm) and 07(yy). My Q now is is there a way (formula) to swap these orders around...?
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

venkat1926

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
I have not understood your problem clarly.
let me see
you enter in a cell e.g.A1
8/17/07 repeat 8/17/07
Do you want this to look like
17,08,07
if this presumption is correct
highlight the cell (A1)
format-number-custom
in the small window type
dd,mm,yy
and click ok.

you can even try in the custom
dd,mmm,yy

If this is not what you want apologies
venkat
 
Upvote 0

RichardU

Board Regular
Joined
Aug 3, 2007
Messages
98
Is there a way to format a date no matter how it is entered? i.e. work out how it was entered then fit it into the format wanted?
 
Upvote 0

Stormseed

Banned
Joined
Sep 18, 2006
Messages
3,274
Is there a way to format a date no matter how it is entered? i.e. work out how it was entered then fit it into the format wanted?

Could you show us the way a date is entered and you want it formatted as a date?
 
Upvote 0

RichardU

Board Regular
Joined
Aug 3, 2007
Messages
98
Well i have a spreadsheet used by many people and they enter it differently

i.e.
ddmmyyyy
dd/mm/yyyy
d/mm/yyyy
dd/m/yyyy
d/m/yyyy
d/m/yy

etc you get the point.

I wanted it to convert the input into the format dd.mm.yyyy no matter how it is entered?
 
Upvote 0

Stormseed

Banned
Joined
Sep 18, 2006
Messages
3,274
well, I dont know if dates entered as "ddmmyyyy" will work.

However, for other entries you can select the entire column and format it as Custom Date as walked thru by Mr. Venkat in his above post. It works perfectly.
 
Upvote 0

Stormseed

Banned
Joined
Sep 18, 2006
Messages
3,274
what happened? what problem you are experiencing? I have tested this on my computer...it works smoothly.

Care to post some details?
 
Upvote 0

Forum statistics

Threads
1,191,587
Messages
5,987,507
Members
440,098
Latest member
MickyMouse123

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