0

I am having such a strange problem when importing data from CMS Supervisor into Excel,

Basically the time is coming out as, for example: 12:30

This is meant to be 1230 seconds, but the format it is coming out as doesn't correspond to this at all.

I've tried every combination of export settings in my vba and it makes no difference, it still ends up formatting the number in this bizzare way.

This is such a simple thing and is driving me crazy! Any help would be greatly appreciated!

Many Thanks,

Uberzen1
  • 415
  • 6
  • 18
  • Can you elaborate some more? What's the data type of the time field? Have you tried removing the `:`? This usually makes excel think the field is an integer instead of a time field. – Wouter Feb 17 '15 at 15:13
  • Hi @Wouter, in the source data it displays correctly, it shows as 1230 seconds, however when importing to excel it sticks the colon in the middle? – Uberzen1 Feb 17 '15 at 15:19

1 Answers1

0

Would it be ok for you to solve this using a formula? That way you can paste the formula in another column like this: =TEXT(A1,"hhmm")
I've taken the assumption that your time field is in A1 Excel

Because my language settings are dutch my formula is =TEXT(A1;"uumm"). Don't worry about that.

Wouter
  • 612
  • 5
  • 14
  • That actually worked perfectly! I knew it would be simple, I think I was otherthinking it! Of course now I will have to be careful with aggregating these up, but that's got me a lot further forwards! Many Thanks! – Uberzen1 Feb 17 '15 at 15:45