Working with Time

Time management in Excel allows you to calculate working hours, track attendance, measure durations, and analyze productivity.

Excel stores time as a fraction of a day:

• 1 day = 1
• 12 hours = 0.5
• 6 hours = 0.25

Time Format in Excel

To work properly with time:

1. Select the cells
2. Go to Format Cells
3. Choose Time or Custom format like:
   • hh:mm
   • hh:mm AM/PM
   • [hh]:mm (for total hours above 24)

Entering Time Correctly

Examples:

• 9:00 AM
• 14:30
• 6:45 PM

Excel automatically recognizes valid time formats.

Basic Time Calculations

1. Calculate Time Difference

If:

• A1 = Start Time (9:00 AM)
• B1 = End Time (5:00 PM)

Formula:

=B1-A1

Result: 8:00 hours

2. Calculate Total Working Hours

If you have multiple entries:

=SUM(C1:C5)

Format the result as:

[hh]:mm

Handling Overnight Time

If work crosses midnight:

Start: 10:00 PM
End: 6:00 AM

Use:

=IF(B1<A1,B1+1,B1)-A1

This correctly calculates overnight duration.

Important Time Functions

1. NOW()

Returns current date and time:

=NOW()

2. TIME()

Creates time manually:

=TIME(9,30,0)

Returns 9:30 AM

3. HOUR, MINUTE, SECOND

Extract parts of time:

=HOUR(A1)
=MINUTE(A1)
=SECOND(A1)

Converting Time to Hours

To convert time into decimal hours:

=A1*24

Format as Number.

Common Uses of Time in Excel

• Attendance tracking
• Payroll calculations
• Shift management
• Project time tracking
• Production monitoring

Important Tips

• Always format results properly.
• Use [hh]:mm for totals exceeding 24 hours.
• Ensure time values are not stored as text.
• Use decimal conversion for payroll calculations.

Working with time in Excel helps automate daily operations and improve accuracy in scheduling and reporting.