date hour format issue

solcosmico

New Member
Joined
Dec 10, 2005
Messages
4
Hello,

I am beginner and having issue with the date hour format.

I have an excel file that I convert into excel format from a csv file. All the columns are "date HH:mm 0-24" format although some lines (not all of them, strangely) remain with the "date HH:mm AM/PM" format visually even if the good format is selected ("date HH:mm 0-24").

We found out that this problem occurs only when the Windows date hour format settings are set to UK, but not when set to USA.

How can i get the format setins to work properly ? anybody ever faced that bug ?

Thanks for your help

??? :eek:
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Could you post some sample data?

I'm a bit confused by the 0-24 bit.

Do you mean you have date/times like this?

10/12/2005 19:20 0-24

If you do I think Excel will regard them as text.

Also how are you importing the data into Excel?

Have you looked at Data>Text to columns.... to seperate out the date/time?
 
Upvote 0
Hi, thanks for your reply :)

I will send some data once I am at the office on monday. Anyway, I didn't explain my problem well, sorry. :oops:

Here are the result I have (at times):

17/11/2005 5:20 PM

instead of:

17/11/2005 17:20

But, in the same sheet (and same column actually), both formats appear in different number despite the fact that I selected the same format for all the column.

I will check the csv import options on monday as well.
 
Upvote 0
Hi!

Here are the csv file:

10/6/2005 3:38 10/6/2005 4:49
9/15/2005 7:18 9/16/2005 10:08
10/19/2005 7:38 10/26/2005 3:15

and here the not satisfaying result when I run my macro:

10/06/2005 09:22:42 10/09/2005 11:27:32
09/07/2005 14:07:11 09/07/2005 15:00:11
09/07/2005 15:24:57 9/17/2005 3:24:57 PM

(the date is different, beside the difference of formats, because of the location settings in windows).

The csv options didn't help... :(

Thank you.
 
Upvote 0
What code are you running?

You say it's a CSV but I can't see any commas.:)
 
Upvote 0
Hi,

I cannot give you an example as the data are confidential. But here is the format csv:

xxx, yyyy, 123456, date hour, zz, 654321
XXX, YYYY, 112233, DATE HOUR, ZZ, 665544

So, ("Date" "Hour"), is only one field, they do part of the same column.

I think we have a bug here as the ("date" "hour") field is randomly displayed with both format ("date" "hour 0-24") or ("date" "hour PM/AM"), when the windows settings are UK-UK. But it works perefctly when the windows settings are USA-UK.

So, to bypass the problem, I thought about writing a macro recognising the text inside the ("date" "hour") field and making some transformations on the "hou" part of the field.

What do you think ? I am not sure of that because I don't know the macro very well.

Thank you for your help :)
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,312
Members
448,564
Latest member
ED38

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
Back
Top