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:
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Norie

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

solcosmico

New Member
Joined
Dec 10, 2005
Messages
4
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.
 

solcosmico

New Member
Joined
Dec 10, 2005
Messages
4
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.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,047
Office Version
  1. 365
Platform
  1. Windows
What code are you running?

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

solcosmico

New Member
Joined
Dec 10, 2005
Messages
4
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 :)
 

Watch MrExcel Video

Forum statistics

Threads
1,118,069
Messages
5,570,010
Members
412,304
Latest member
citrus
Top