CreateDATE, TIME, DATEVALUE and TIMEVALUE
ExtractDAY, MONTH, YEAR, HOUR, MINUTE and SECOND
MeasureDATEDIF, subtraction and YEARFRAC
ScheduleEDATE, EOMONTH, WORKDAY and NETWORKDAYS
1 Understanding How Excel Stores Dates and Times
Excel normally stores a date as a serial number and displays that number using a date format. Time is stored as a fraction of one day.
This design allows Excel to add days, subtract dates, compare deadlines and calculate working durations. A value that only looks like a
date but is stored as text will not behave reliably in formulas, sorting or PivotTables.
Definition: A valid Excel date is a numeric serial value displayed through a date format; a valid time is a fractional part of a day.
Dates, Display Formats and Regional Settings
The same date can be displayed as 15-Aug-2026, 15/08/2026 or August 15, 2026 without changing the stored value. Problems arise when
users enter ambiguous text such as 04/05/2026 because different systems may interpret the month and day differently. For shared
workbooks, use clear formats such as dd-mmm-yyyy and validate imported data before analysis.
Professional Tip: Store dates as genuine Excel values and control appearance through formatting. Do not type extra words directly into cells that must be used in calculations.
Practical Experiment 1: Identify Real Dates
Enter or paste a mixture of valid dates, text dates and blank cells into a worksheet.
Step 1: ObserveCompare alignment, number format and formula-bar values for each record.
Step 2: TestUse =ISNUMBER(A2) to identify whether the value is stored numerically.
Step 3: RecordMark each row as valid date, text date, invalid entry or blank.
Learning Output: You will distinguish a genuine date value from text that only looks like a date.
2 TODAY, NOW and DATE
TODAY returns the current date, while NOW returns the current date and time. Both are volatile functions, meaning they recalculate
when the workbook recalculates. DATE constructs a valid date from separate year, month and day values, making it safer than joining
date parts as text.
Caution: TODAY and NOW update automatically. Do not use them when a permanent historical timestamp is required unless you intentionally convert the result to a fixed value.
Practical Experiment 2: Live Deadline Tracker
Create task names and due dates, then calculate current status automatically.
Step 1: CreateEnter five tasks with past, current and future due dates.
Step 2: CalculateUse due date minus TODAY to calculate days remaining.
Step 3: ClassifyUse IF to return Overdue, Due Today or Open.
Learning Output: You will build a date-driven status report that changes automatically.
3 DAY, MONTH, YEAR and Calendar Components
DAY, MONTH and YEAR extract numeric components from a valid date. These functions help create monthly summaries, yearly comparisons,
financial-year classifications and age-analysis fields. WEEKDAY and WEEKNUM support calendar-based reporting and scheduling.
Creating Reporting Periods
A monthly sales report may require a month label, quarter, calendar year and financial-year category. Extracting components allows
these fields to be created consistently. Remember that a TEXT result is excellent for labels but should not replace a genuine date
when later date calculations are required.
DAYDay number
MONTHMonth number
YEARCalendar year
WEEKDAYDay-of-week index
WEEKNUMWeek number
Practical Experiment 3: Build Reporting Columns
Use a transaction-date column to create analysis-ready date dimensions.
Step 1: ExtractCreate day, month and year columns using date functions.
Step 2: LabelCreate month-year and weekday labels using TEXT.
Step 3: AnalyzeSummarize the number of transactions by month and weekday.
Learning Output: You will prepare date dimensions for PivotTables and dashboards.
4 Calculating Age, Experience and Duration
Subtracting one date from another returns the number of elapsed days. DATEDIF can return completed years, months or days between
two dates, while YEARFRAC estimates the interval as a fraction of a year. Choose the method according to the business definition,
not merely according to the shortest formula.
Business Rule: Define whether the start day, end day, weekends or partial periods must be included. Two formulas can be technically correct yet answer different operational questions.
Practical Experiment 4: Employee Service Analysis
Create employee names, joining dates and service metrics.
Step 1: CalculateReturn completed service years with DATEDIF.
Step 2: DetailCalculate remaining months after completed years if required.
Step 3: ClassifyGroup employees into service bands such as Below 1 Year, 1–3 Years and 3+ Years.
Learning Output: You will create an HR service-tenure report using dynamic date logic.
5 EDATE and EOMONTH for Month-Based Planning
EDATE moves a date forward or backward by a specified number of months. EOMONTH returns the last date of a month after moving by a
specified number of months. These functions are valuable for renewal schedules, instalment dates, subscription periods, reporting
cut-offs and month-end accounting.
1Start DateIdentify the transaction, joining, invoice or contract date.
2Month OffsetDecide how many months to move forward or backward.
3Boundary RuleChoose same-day logic with EDATE or month-end logic with EOMONTH.
4Status CheckCompare the calculated date with TODAY to mark upcoming or overdue items.
Practical Experiment 5: Renewal Schedule
Create customer names, activation dates and subscription periods in months.
Step 1: CalculateUse EDATE to return the renewal date.
Step 2: CompareCalculate days remaining using renewal date minus TODAY.
Step 3: AlertCreate a Renewal Due Soon status for items due within 30 days.
Learning Output: You will build an automatic month-based renewal tracker.
6 NETWORKDAYS and WORKDAY
NETWORKDAYS counts working days between two dates, normally excluding Saturdays and Sundays. WORKDAY returns a future or past working
date after adding a specified number of workdays. Both functions can use a holiday range. Their INTL versions allow custom weekend patterns.
Remember: NETWORKDAYS normally includes both start and end dates when they are working days. Confirm the required business policy before interpreting the result.
Practical Experiment 6: Service-Level Deadline
Calculate a promised completion date after a fixed number of working days.
Step 1: PrepareCreate request dates and a separate holiday list.
Step 2: ScheduleUse WORKDAY to calculate the committed completion date.
Step 3: MeasureUse NETWORKDAYS to calculate actual working days used.
Learning Output: You will calculate practical deadlines that exclude non-working days.
7 Time Functions and Attendance Calculations
HOUR, MINUTE and SECOND extract parts of a time value. TIME constructs a valid time from separate components. Subtracting an in-time
from an out-time returns a fraction of a day; multiplying by 24 converts that duration into decimal hours. For shifts crossing midnight,
MOD can prevent a negative result.
Time Formats for Durations
The format h:mm displays a time of day and may reset after 24 hours. The format [h]:mm displays accumulated
hours beyond 24, making it more appropriate for weekly attendance totals. Decimal hours are useful for productivity and payroll calculations,
while [h]:mm is often easier to read.
Practical Experiment 7: Attendance Hours
Create employee in-time, out-time and break duration fields.
Step 1: CalculateSubtract in-time and break time from out-time.
Step 2: FormatDisplay duration as [h]:mm and also calculate decimal hours.
Step 3: FlagMark records below the required working-hour threshold.
Learning Output: You will create a reliable daily attendance-duration report.
8 Converting Text Dates and Times
Imported dates and times may arrive as text. DATEVALUE converts a recognized text date into a date serial number, while TIMEVALUE converts
recognized text time into a time value. Text to Columns, Power Query and controlled formulas may be safer for large or inconsistent datasets.
Data Quality Rule: Preserve the original imported column, create a cleaned date column and test a sample of records before replacing or reporting from the transformed data.
Practical Experiment 8: Repair Imported Dates
Use a sample containing dd/mm/yyyy text, month names, blank cells and invalid entries.
Step 1: DiagnoseUse ISNUMBER and visual checks to identify text dates.
Step 2: ConvertApply DATEVALUE or a component-based DATE formula according to the pattern.
Step 3: ValidateCompare converted dates with the source and flag invalid values for review.
Learning Output: You will convert imported date text without losing auditability.