HI all,

A question I hope will be nice and easy for you!

I am importing data from an inhouse data programe to Excel. One of the columns is a flight departure time. From this sheet I need to mail merge to a word document which extracts many of the fields including the departure time. However, the time always displays as hh:mm:ss. I only need to show hh:mm. I have tried changing in the format but in the formula bar is still shows hh:mm:ss which is what is being merged.

Please help!!! this will save us a lot of time in the office as we currently hand write everything! I'm not that savvy on jargon either so please kindly keep it simple for me!!

Many thanks - Jamester

Howy, and welcome to the board. It may be that Excel is considering as text, not a (serial) number. One way to convert is to type the number 1 in a blank cell, copy it, then select the column with dates, and Paste Special > Multiply. This will convert them to true numbers, and then apply formatting as date as needed.

Hey..!
Thanks for the advice.. I tried it but alas it doesn't work. It changes a time of 23:30 to 0.98! Can't tell someone they'll be picked up at 0.98hrs!

Any other ideas?

Thanks
J

That's because it is now a serial number. 23:30 is the equivalent of the fraction of the day 0.98.

Also, are you wanting only the 23:30, and not the date with it? If so, then format as Time rather than Date, and choose the hh:mm option.

And if you have an extra column, you can use this formula to get what you want: Assume that your date is in cell B3, then in C3, put this:

=HOUR(B3)&":"&TEXT(MINUTE(B3),"00")