Date and Time formating

[ymredzu]

Dear All,

I have a question ..... Here i have attached a sheet that contains about 5 columns with date and time but for some reason the column B and C is not capturing the format that i want ...... i would like to have the format as same as column D,E,F

Thanks in ADV.

[benishiryo]

hi ymredzu. your dates in column B & C are actually texts. are these data entries? i can only do so much if there are human errors in them. in another column, try:

Please Login or Register  to view this content.

drag the formula down. it solves most of the problems. currently, your date & time keyed in column B & C are not in sync with the computer region settings. suppose it's DMY, user keyed as MDY. the better way to key dates is to type in DDMMMYYYY like 29Mar2013. you'll never get it wrong. the columns also contains extra spaces, a full stop instead of colons for time & even a carriage return (using ALT + ENTER).

the only problem left is in row 35. that data is very inconsistent with the rest, so you probably have to manually change it

[ymredzu]

Hi benishiryo,

Thanks that really worked ........ " your dates in column B & C are actually texts. are these data entries?" yup more or less this data was extracted from a sheet with a VB script... maybe if you want i can share it with you..... if you want?

But anyways this is good enough, this is what i need.

Thanks.