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

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,344
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,344
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 :)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,994
Messages
5,834,773
Members
430,320
Latest member
Napzz

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