2

I am trying to format an integer column to a time column, but the values all end up as 12 like this:

12

12

12

enter image description here

2
  • 2
    Dates are stored with integers for day numbers and times as fractions of a day. If the integers represent the hour of the day, divide by 24, then format as time. – AFH Jun 29 '15 at 13:47
  • Wow that works. Care to post it as a reply, so I can accept? :) – giorgio79 Jun 29 '15 at 13:52
1

If you don't want to deal with division and other math to get to the decimal time value, you can use the TIME function which will handle it all for you:

Usage for your example

TIME function

MS reference: https://support.office.com/en-nz/article/TIME-function-9a5aff99-8f7d-4611-845e-747d0b8d5457

1

In Excel, dates are stored with integers for day numbers (since 1900) and times as fractions of a day.

If your integers represent the hour of the day, divide them by 24, then format as time.

2
  • I really like this idea. Meanwhile, someone posted the TIME function, which I also love and find quite powerful. – giorgio79 Jun 29 '15 at 20:00
  • Yes, I didn't think of the TIME function, and I agree it's a better answer. – AFH Jun 29 '15 at 20:38
0

In Excel, times are stored as the fraction of a day. Therefore, each hour is 1/24 of a day. To convert integer hours to a time, divide the time by 24 and format as a date and/or time.

More info:

Excel stores dates as sequential serial numbers so that they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,447 days after January 1, 1900.

Numbers to the right of the decimal point in the serial number represent the time; numbers to the left represent the date. For example, the serial number 0.5 represents the time 12:00 noon.

https://support.office.com/en-us/article/NOW-function-3337fd29-145a-4347-b2e6-20c904739c46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.