Clocking in: using excel's date and time formulas.

• Author: Lenning, Jeff
• Position: TechTalk

Have you ever tried w do mw type of calculation with a time value in Excel, only to receive a strange result? Two examples BR adding up boars on a time sheet and multiplying the number or hours worked by a pay rate. If you've tried wine thing like (his and encountered an unexpected result, the solution is fairly simple.

Dates and times are stored as decimal values, where the value to the left of the decimal represents the date and the value to the right represents the lime. So, when you enter a date such as Jan. 1, 2017, Excel stores the value 42736. The date part is fairly easy to visualize, because the number 1 represents 1/1/1900, and Excel just adds 1 for each day.

The time pan is more interesting Since there are 24 hours in a day, Excel converts the number or hours into a decimal value by dividing by 24. For example, 6 a.m. is six hours, and six divided by 24 is 0-25. Noon is 0.5 and 6 p.m. is 0.75.

We can apply a variety of formats to display the stored value as desired. Since a cell's default format is "General," if you enter the value 42736 into a cell, you see that value. But, if you formal the cell with one of the many "Date" formats available, then you see the expected date, such as 1/1/17 or January 1, 2017. When you enter a date that Excel recognizes, such as "1/1/17" if you cell, Excel stores the underlying date serial number.

The same applies to time*. If you enter the value 0.25 into a cell dial has "General" formatting then you see the value 0,25, But, if you apply a time format, then you see the displayed time value, such as 6 a.m.

So far, so good? We're just about done with the backstory, and then we'll see how all or this (its together. We know that we can apply a date or time format to display the desired date or time. But what happens if the stored value includes both a date and time? For example, 42736.25.

It depends on the formatting. We could apply a date/time format to display both parts. If we apply a date format. Excel displays the date (value to the left of the decimal) but not the lime (value to the right of the decimal).

Likewise, if we apply a time formal, Excel displays the time, but not the date. When a lime format is applied, the time of day is displayed. Remember this, because we'll come back to it momentarily.

Let's say we have a time sheet in Excel, it's in the middle of busy season and we're working crazy, 12-hour days. We enter 12:00 hours into five cells for Monday-Friday When we write a formula to...