Subscriber only lesson.

Sign up to the Microsoft Excel - Basic & Advanced course to view this lesson.

## About this lesson

Understand how time works in Excel, and how to change from whole numbers into time increments and back again.

## Lesson versions

Multiple versions of this lesson are available, choose the appropriate version for you:

2013, **2016**.

## Exercise files

Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.

Understanding Time.xlsx14.4 KB Understanding Time - Completed.xlsx

14.7 KB

## Quick reference

### Topic

Understanding how time works in Excel, and how to convert from whole numbers into time increments and back.

### When to use

Manipulation of numbers into time formats and back is very common in timesheets.

### Instructions

#### Key Understandings

- Times are fractions of days
- .25 is equivalent to .25 days, or 6 hours
- .50 is equivalent to .50 days, or 12 hours
- Time fractions can be added to a whole date, so 41,633.5 is Noon on Dec 25, 2013
- If a time is entered in a cell without a date, it will default to the time on Jan 1, 1900

#### Converting whole numbers to time (so they can be formatted as time)

=A1/24 |
Converts the value in A1 into the fractional equivalent that represents hours (24 hours in a day) |

=A1/24/60 |
Converts the value in A 1 into the fractional equivalent that represents minutes (60 minutes in an hour) |

=A1/24/60/60 |
Converts the value in A 1 into the fractional equivalent that represents seconds (60 seconds in an hour) |

#### Converting time fractions back to whole values (so they can be represented as days/hours/minutes)

=A1*24 |
Converts the fractional time value in A1 into whole hours (24 hours in a day) |

=A1*24*60 |
Converts fractional time value in A1 into whole minutes (60 minutes in an hour) |

=A1*24*60*60 |
Converts fractional time value in A1 into whole seconds (60 seconds in a minute) |

Lesson notes are only available for subscribers.