DEPARTMENT OF ARTIFICIAL INTELLIGENCE & MACHINE
LEARNING
AND
DEPARTMENT OF ARTIFICIAL INTELLIGENCE & DATA
SCIENCE
Data Analysis with Excel
Module -5
Mastering Time Values and Conditional
Formatting in Excel
DEFAULT DATE FORMAT IN EXCEL
TIME IN EXCEL
Times are stored in Excel as decimals, between .0 and .99999, that
represent a proportion of the day where .0 is 00:00:00 and .99999 is
23:59:59.
For example:
0.25 is 06:00 AM
0.5 is 12:00 PM
0.541655093 is 12:59:59 PM
DATES & TIMES IN EXCEL
Excel stores dates and times as decimal numbers comprised of an
integer representing the date and a decimal portion representing the
time.
For example:
1.25 is January 1, 1900 6:00 AM
42005.5 is January 1, 2015 12:00 PM
HOW TO CONVERT DATE TO NUMBER
DATEVALUE AND TIMEVALUE FUNCTIONS
HOW TO CHANGE THE DEFAULT DATE AND TIME
FORMATS
go to Control panel and click Region and Language. If in your Control
panel opens in Category view, then click Clock, Language, and
Region > Region and Language > Change the date, time, or number
format.
On the Formats tab, choose the region under Format,
CONDITIONAL FORMATTING FOR DATES
(BUILT-IN RULES)
Microsoft Excel provides 10 options to format selected cells based on
the current date.
To apply the formatting, you simply go to the Home tab > Conditional
Formatting > Highlight Cell Rules and select A Date Occurring.
Excel conditional formatting built-in rules for dates
CALCULATE DIFFERENCE BETWEEN TWO
DATE USING THE FUNCTION 'DATE-DIF'
DIFFERENCE BETWEEN TWO GIVEN DATE TIMES
IN DAYS, HOURS, AND MINUTES
TASK-1 START THE EXCEL WITH TIME DATA
Enter the twenty five different times in cells A1 to A25
TASK-2 USE THE BASIC TIME FUNCTIONS
In column B, use the HOUR() function to extract the hours.
In column C, use the MINUTE() function to extract the minutes.
In column D, use the SECOND() function to extract the seconds.
TASK-3 TIME ADDITION AND SUBTRACTION
Add 3 hours and 45 minutes to 8:30 AM.
Subtract 2 hours and 30 minutes from 3:15 PM.
Find the difference between 7:00 PM and 12:45 PM.
TASK-4 APPLYING CONDITIONAL FORMATTING RULES
Set the conditions:
For times before 12:00 PM:
Format cells if the cell value is less than 12:00 PM
For times between 12:00 PM and 6:00 PM:
Format cells if the cell value is between 12:00 PM and 6:00 PM.
For times after 6:00 PM:
Format cells if the cell value is greater than 6:00 PM.
TASK-5 USE A FORMULA TO DETERMINE WHICH CELLS TO FORMAT
Set the conditions:
For column A (exactly 12:00 PM): =A1=TIME(12, 0, 0).
For column B (hour greater than 6): =HOUR(B1)>6.
For column C (minute less than 30): =MINUTE(C1)<30.
TASK -6 CREATE A CALENDAR
Create a Month / Year calendar, Add Borders and format the
Calendar with different font styles, sizes, and colors to enhance the
appearance of your calendar. Highlight weekends or holidays in
different colors to make them stand out.
CASE STUDY – TRACK EMPLOYEE WORKING HOURS
Assume that you are the HR manager at a mid-sized company, and
you've been asked to track employee working hours, calculate
overtime, and ensure the payroll process is accurate.
OBJECTIVE
• Track employee working hours.
• Calculate total working hours for each employee and calculate
overtime hours (if any).
• Apply conditional formatting to highlight employees who exceed
regular working hours.
CREATE COLUMNS
Employee ID
Employee Name
Date
Time in
Timeout
Regular Hours
Total Hours Worked
Overtime Hours
CONDITIONAL FORMATTING
• Steps:
• Select the "Overtime Hours" column.
• Go to Home > Conditional Formatting > New
Rule.
• Choose "Format only cells that contain".
• Set the condition to be greater than "0:00".
• Choose a format (e.g., a red fill) to highlight overtime
hours.
•Select the "Time In" column.
•Apply conditional formatting where the cell value is greater than "09:00 AM".
CASE STUDY: PROJECT DEADLINE
MANAGEMENT
Objective
• Track the start and deadline dates of tasks.
• Calculate the number of days remaining until the deadline.
• Flag overdue tasks.
• Calculate task completion time and compare it with the estimated
duration.
• Apply conditional formatting to highlight tasks that are at risk of being
delayed or have already missed their deadlines.
CREATE A TABLE WITH THE FOLLOWING
COLUMNS:
• Task ID: Unique identifier for each task.
• Task Name: The name of the task.
• Start Date: The date when the task starts.
• Deadline: The date by which the task must be completed.
• Estimated Duration (Days): The expected number of days to
complete the task.
• Actual Completion Date: The date when the task was actually
completed.
• Days Remaining: The number of days left until the deadline.
• Status: The current status of the task (e.g., "On Track," "Overdue,"
"Completed").
Calculating Days Remaining Until the Deadline
=[@Deadline] - TODAY()
Calculating Task Completion Time
=Completion date-start date
Determining the Status of the Task
=IF([@Actual_Completion_Date] <> "", "Completed",
IF([@Days_Remaining] < 0, "Overdue", "On Track"))
APPLYING CONDITIONAL FORMATTING
Select the "Status" column.
Go to Home > Conditional Formatting > New Rule.
Choose "Format only cells that contain".
Set the condition to "Cell Value" equal to "Overdue".
Choose a red fill to highlight overdue tasks.
"AS YOU DELVE INTO DATA SCIENCE,
MAY EXCEL BE THE TOOL THAT MAKES
YOUR ANALYTICAL SKILLS SHINE."
Thank You
Happy Learning