CODEWITHSUNDEEP
excelexcel-2010
iso_9001_
iso_9001_

Reputation: 2859

how to prevent excel 2010 from converting times to decimals automatically when cell is pasted

As you might have come accross, MS Excel tends to convert times to decimal values. I do want it to convert the values automatically because I need the time value. Suppose I have following data:

Departure | Time

Istanbul 06:45 Ankara 01:30

I am using Concatenate function to create a desired string as Istanbul: 08:00 and Ankara: 18:30. However, when I use the formula, Excel converts hours to decimals and I get Istanbul: 0.28125 and Ankara: 0.0625. I do not want it to convert. How can I do this?

ps: This also happens when I copy time values from Excel to Notepad++. Moreover, when I import time values into PostgreSQL through add-in, I still get decimal values in the columns

Upvotes: 3

Views: 8891

Answers (1)

Dave Sexton
Dave Sexton

Reputation: 11188

You want to use the TEXT function to convert the time into the text format that you require. Something like this:

=CONCATENATE("Istanbul: ", TEXT(A1,"hh:mm"), " and Ankara: "18:30, TEXT(A2,"hh:mm"))

Upvotes: 6

Related Questions