Help with importing data - negative values not displaying correctly

[Toosh]

Hi there

New to the forums, so this is my first post. Sorry to jump straight in feet first!

I've been asked to import data from our works flexi system, but am experiencing problems with some of the values during the import.

The data is generally of the following format:

Staff_Name, current flexi time, total flexi time, min flexi time, max flexi time

A sample of the data would be

F Blogs, -03:45, 02:50, -05:00, 15:00

-03:45 = this shows the person is currently minus 3hrs 45 mins (each days starts with -07:24 hrs)
02:50 = this shows that overal, the person is 2hrs 50 mins in credit
-05:00 = staff are only allowed to be a -05:00 hrs down at the end of the month
15:00 = staff are allow to accrue a max of 15 hrs in credit.

When I import this data using standard comma delimited, all of the data is correct apart from some of the negative values. When I check the format of the cells, all of them are GENERAL however, using the above example -03:45 displays a VALUE! error but the -05:00 displays correctly.

Notwithstanding that these values relate to time, I was expecting them to be simply imported as is and do not understand why the times that reflect a negative value for [current_flexi_time] should be any different to the other negative values also shown in the spreadsheet. I have tried to change the format of the cell to time or even number but it still won't work. It also makes no difference deleting the cell contents, changing the cell format to GENERAL and then retyping the negative value.

One thing I have noticed, is that when clicking in the field, it highlights row 3 to row 45 (the numbers that incidentally represent the time! ) and seems to think there should be a formula, which I guess is why it shows VALUE! ERROR as it would not be a valid formula.

I am using Excel 2007, but this will ultimately be carried out by a user who has Excel 2003.

Any help or suggestions would be gratefully received.

Cheers

T

[DonkeyOte]

Please post a sample of the data removing the names associated beforehand so as to avoid confideniality issues (ie file containing only the time).

If you're able to provide the import file also that would be handy (again with names removed).

Generally speaking you can't display negative times in XL (unless running 1904 Date System) ... you may need to store as Text, this you can do if you strip the .csv extension from your file prior to opening in XL as you will then be presented with the text import wizard at which point you can specify the time columns as Text... and work with the values thereafter (ie *24 to move into decimal format).