Date & Time Functions — Complete Guide (7 Topics)

Excel mein date aur time master karo — TODAY se lekar NETWORKDAYS tak. Age calculate karo, working days count karo, dates ka arithmetic karo — Data Insights par.

📑 Is Part 5 Mein Aap Kya Sikhenge:

• Topic 1: TODAY, NOW — Current Date & Time
• Topic 2: DATE, YEAR, MONTH, DAY — Date Parts
• Topic 3: DATEDIF — Difference Between Dates
• Topic 4: WEEKDAY, WEEKNUM — Day & Week Info
• Topic 5: EOMONTH, EDATE — Month Calculations
• Topic 6: NETWORKDAYS, WORKDAY — Business Days
• Topic 7: TEXT with Dates — Custom Formatting

📋 Note: Same Employee Database use karenge (10 employees). Join Date column (F) pe date functions apply karenge — age calculate karna, tenure nikalna, working days count karna.

🔍 Definition: TODAY() returns the current date only (no time). NOW() returns both current date and time. Both are volatile functions — they auto-update every time the worksheet recalculates.

🎯 Samjho Simple Bhasha Mein: TODAY() aaj ki tareekh deta hai — hamesha update rehta hai. NOW() date + time dono deta hai. Age calculations, days remaining, tenure — sab TODAY() se hote hain. Dynamic hain — kal open karoge toh kal ki date automatically aayegi. Isliye reports mein live data ke liye perfect.

💡 Syntax:

=TODAY() → Sirf date (15/03/2024)
=NOW() → Date + time (15/03/2024 14:30)

Important: Dono functions ke koi arguments nahi hain — brackets khali!

Shortcuts:
Ctrl + ; → Static date (never updates)
Ctrl + Shift + ; → Static time

💻 Real-World Examples:

// Basic usage: =TODAY() // 15/03/2024 =NOW() // 15/03/2024 14:30:25
// Days since employee joined:
=TODAY() - F2
// Rahul joined 15-Mar-2021
// Today = 15-Mar-2024 → Result: 1096 days

// Years of experience:
=(TODAY() - F2) / 365
// Result: ~3 years

// Rounded to whole years:
=INT((TODAY() - F2) / 365)
// Result: 3

// Days until year
end:
=DATE(YEAR(TODAY()), 12, 31) - TODAY()
// Days remaining in current year

// Retirement date (60 years after birth):
=EDATE(F2, 12*60)
// 60 years added to
join date

// Check if employee is new (joined last 90 days):
=IF(TODAY() - F2

// Time-based greeting:
=IF(HOUR(NOW())

⚡ Important: TODAY() aur NOW() volatile functions hain — file open karte hi update ho jaate hain. Agar static date chahiye (kabhi change na ho) toh Ctrl + ; shortcut use karo — direct value insert karta hai.

⚠️ Common Mistakes:

• Mistake: TODAY() ko static date samajhna → Kal open karoge toh new date aayegi!
  Fix: Static date chahiye toh Ctrl + ; use karo.
• Mistake: TODAY(A1) likhna → Arguments nahi hote!
  Fix: Empty brackets: TODAY() aur NOW()
• Mistake: Age calculation mein 365 se divide karna — leap years account nahi hote.
  Fix: DATEDIF use karo exact age ke liye (Topic 3).

💬 Interview Questions:

🔍 Definition: DATE(year, month, day) builds a date from separate parts. YEAR(), MONTH(), DAY() extract individual components from a date. These are the building blocks of all date calculations.

🎯 Samjho Simple Bhasha Mein: DATE se date banate ho — pieces jodhke. YEAR/MONTH/DAY se date todhte ho — parts nikalne ke liye. Jaise "15-Mar-2021" mein YEAR = 2021, MONTH = 3, DAY = 15. Bahut common use — birthday se sirf month nikalna, joining year filter karna, month-wise reports banana.

💡 Syntax:

=DATE(year, month, day) → Build date
=YEAR(date) → Extract year (2021)
=MONTH(date) → Extract month (1-12)
=DAY(date) → Extract day (1-31)

Smart Feature: DATE handles overflow automatically. DATE(2024, 13, 1) = January 2025. DATE(2024, 2, 30) = March 2 (30 Feb doesn't exist).

💻 Real-World Examples:

// DATE — Build a date: =DATE(2024, 3, 15) // 15-Mar-2024 =DATE(2024, 12, 31) // 31-Dec-2024
// Auto-adjust overflow:
=DATE(2024, 13, 1) // 1-Jan-2025 (13th month = next Jan)
=DATE(2024, 2, 30) // 1-Mar-2024 (Feb has 28/29)

// Extract parts
from
join date:
=YEAR(F2) // 2021
=MONTH(F2) // 3
=DAY(F2) // 15

// Real Use — First day of employee's join month:
=DATE(YEAR(F2), MONTH(F2), 1)
// Rahul: 1-Mar-2021

// Employee's
join year:
=YEAR(F2)
//
Group employees by joining year

// Filter employees who joined in 2021:
=IF(YEAR(F2)=2021, "2021 Batch", "Other")

// Count employees joined per year:
=SUMPRODUCT((YEAR(F2:F11)=2021)*1)
// 2021 mein kitne joined

// Age
from birth year (age table):
=YEAR(TODAY()) - YEAR(F2)
// Simple year diff — not exact age

// Month name
from date:
=TEXT(F2, "mmmm")
// "March"

📊 Practice Results:

⚠️ Common Mistakes:

• Mistake: DATE argument order galat → DATE(15, 3, 2024) → weird date!
  Fix: Order: YEAR, MONTH, DAY. Yaad rakho: YMD sequence.
• Mistake: MONTH() text return karega expect karna → MONTH deta hai NUMBER (1-12), name nahi.
  Fix: Month name ke liye TEXT(date, "mmmm") use karo.

💬 Interview Questions:

🔍 Definition: DATEDIF calculates the difference between two dates in years, months, or days. Perfect for calculating age, tenure, or duration between events. It's a "hidden" function — Excel doesn't show it in autocomplete but it works!

🎯 Samjho Simple Bhasha Mein: Age calculate karna hai? Tenure nikalna hai? DATEDIF sabse accurate hai. Simply (TODAY - Birthday)/365 use karo toh leap years account nahi hote. DATEDIF exact age deta hai — years, months, days alag alag. HR analytics ka hero function hai!

💡 Syntax:

=DATEDIF(start_date, end_date, "unit")

Units:
"Y" → Complete years
"M" → Complete months
"D" → Complete days
"YM" → Months excluding years
"MD" → Days excluding months
"YD" → Days excluding years

Important: Start date must be BEFORE end date, warna #NUM! error!

💻 Real-World Examples:

// Basic — Years of experience: =DATEDIF(F2, TODAY(), "Y") 
// Rahul: 3 years (joined 15-Mar-2021)

// Total months of service:
=DATEDIF(F2, TODAY(), "M")

// Rahul: 36 months

// Total days worked:
=DATEDIF(F2, TODAY(), "D")

// Rahul: 1096 days

// Complete tenure: "X years Y months":
=DATEDIF(F2,TODAY(),"Y")&" years "&DATEDIF(F2,TODAY(),"YM")&" months"

// Rahul: "3 years 0 months"

// Full tenure: "X years Y months Z days":
=DATEDIF(F2,TODAY(),"Y")&"Y "&DATEDIF(F2,TODAY(),"YM")&"M "&DATEDIF(F2,TODAY(),"MD")&"D"

// "3Y 0M 0D"

// Age calculation (birthday in cell A1):
=DATEDIF(A1, TODAY(), "Y")

// Exact age in years

// Days until retirement (60 years):
=DATEDIF(TODAY(), EDATE(F2, 12*60), "D")

// Categorize by tenure:
=IF(DATEDIF(F2,TODAY(),"Y")>=5, "Senior",
IF(DATEDIF(F2,TODAY(),"Y")>=2, "Mid", "Junior"))

// Anniversary date (next anniversary):
=DATE(YEAR(TODAY()), MONTH(F2), DAY(F2))

📊 Employee Tenure Analysis:

⚠️ Common Mistakes:

• Mistake: Start date end date se baad ki dena → #NUM! error.
  Fix: Always start_date < end_date. Use IF check karo.
• Mistake: DATEDIF autocomplete mein nahi dikhta → Doubt hota hai function exist karta hai.
  Fix: Type karo manually — kaam karta hai! Hidden legacy function hai.
• Mistake: Unit code galat likhna → "y" chalta hai but "years" nahi.
  Fix: Sirf specified codes use karo: Y, M, D, YM, MD, YD.

💬 Interview Questions:

🔍 Definition: WEEKDAY returns the day of the week as a number (1-7). WEEKNUM returns the week number of the year (1-53). Useful for scheduling, planning, weekly reports, and identifying weekends.

🎯 Samjho Simple Bhasha Mein: WEEKDAY batata hai kaunsa din hai — Monday, Tuesday, etc. WEEKNUM batata hai year ka kaunsa week hai — 1st week, 2nd week, etc. Weekend detection, weekly reports, meeting scheduling — sab mein use hota hai. Return type customize kar sakte ho — Sunday se start ya Monday se.

💡 Syntax:

=WEEKDAY(date, [return_type])
=WEEKNUM(date, [return_type])

WEEKDAY return types:
1 → Sunday=1, Saturday=7 (default)
2 → Monday=1, Sunday=7
3 → Monday=0, Sunday=6

WEEKNUM return types:
1 → Week starts Sunday (default)
2 → Week starts Monday
21 → ISO 8601 week

💻 Real-World Examples:

// WEEKDAY basics: =WEEKDAY(F2) 
// 2 (Monday if Sunday=1) =WEEKDAY(F2, 2) 
// 1 (Monday if Monday=1)

// Get day name:
=TEXT(F2, "dddd") 
// "Monday"
=TEXT(F2, "ddd") 
// "Mon"

// Get day name using CHOOSE:
=CHOOSE(WEEKDAY(F2), "Sun","Mon","Tue","Wed","Thu","Fri","Sat")

// Check if weekend:
=IF(WEEKDAY(F2, 2)>5, "Weekend", "Weekday")

// Return type 2 mein: Sat=6, Sun=7

// WEEKNUM — Week number of year:
=WEEKNUM(F2) 
// e.g., 11 (11th week)
=WEEKNUM(F2, 2) 
// Week starts Monday
=WEEKNUM(TODAY()) 
// Current week number

// ISO week number (international standard):
=ISOWEEKNUM(F2)

// Group data by week (for weekly reports):
="Week " & WEEKNUM(F2) & " of " & YEAR(F2)

// "Week 11 of 2021"

// Count employees joined on Mondays:
=SUMPRODUCT((WEEKDAY(F2:F11, 2)=1)*1)

// Highlight weekends (Conditional Formatting formula):
=WEEKDAY(F2, 2)>5

// Returns TRUE for Sat/Sun

⚠️ Common Mistakes:

• Mistake: WEEKDAY default mein Sunday=1 hai — logic galat lag sakti hai.
  Fix: Return type 2 use karo Monday=1 ke liye — more intuitive.
• Mistake: WEEKNUM aur ISOWEEKNUM different results — international standard alag hai.
  Fix: Global reports mein ISOWEEKNUM use karo.

💬 Interview Questions:

🔍 Definition: EOMONTH returns the last day of a month (X months from a given date). EDATE returns the same day of the month, X months away. Perfect for month-end reporting, subscription renewals, and financial calculations.

🎯 Samjho Simple Bhasha Mein: Month end nikalna? EOMONTH. 6 months later date? EDATE. Financial reports monthly banti hain — 30/31 day month end automatically calculate. Subscription renewals, EMI dates, quarterly reports — sab mein use hota hai.

💡 Syntax:

=EOMONTH(start_date, months) → Last day of month
=EDATE(start_date, months) → Same day, X months later

months: Positive = future, Negative = past, 0 = same month

Examples:
EOMONTH(TODAY(), 0) → Current month's last day
EOMONTH(TODAY(), -1) → Previous month's last day
EDATE(TODAY(), 6) → 6 months from today

💻 Real-World Examples:

// EOMONTH —
End of month: =EOMONTH(TODAY(), 0) // Current month
end =EOMONTH(TODAY(), 1) // Next month
end =EOMONTH(TODAY(), -1) // Previous month
end =EOMONTH(TODAY(), 11) //
End of Dec (11 months from Jan)
// First day of current month:
=EOMONTH(TODAY(), -1) + 1
// Previous month
end + 1 day

// Employee's first month-
end after joining:
=EOMONTH(F2, 0)
// Rahul joined 15-Mar-21 → Result: 31-Mar-21

// EDATE — Same day, months later:
=EDATE(TODAY(), 6) // 6 months
from today
=EDATE(TODAY(), -3) // 3 months ago
=EDATE(F2, 12) // 1 year after
join
=EDATE(F2, 12*3) // 3 years after
join

// Retirement date (age 60):
=EDATE(F2, 12*60)
// 60 years (720 months)
from
join date

// Probation
end (3 months from joining):
=EDATE(F2, 3)
// Rahul: 15-Jun-2021

// Next appraisal (annual):
=EDATE(F2, 12)
// Anniversary date

// Quarter
end dates:
=EOMONTH(DATE(YEAR(TODAY()),3,1), 0) // Q1
end
=EOMONTH(DATE(YEAR(TODAY()),6,1), 0) // Q2
end
=EOMONTH(DATE(YEAR(TODAY()),9,1), 0) // Q3
end
=EOMONTH(DATE(YEAR(TODAY()),12,1), 0) // Q4
end

// Days remaining in current month:
=EOMONTH(TODAY(), 0) - TODAY()

⚠️ Common Mistakes:

• Mistake: EDATE ka result 31 wale month se 30 wale month mein jaana → 31-Jan + 1 month = 28-Feb, not 31-Feb!
  Fix: Excel automatically adjusts to last valid day. This is expected behavior.
• Mistake: EOMONTH mein 0 vs 1 confuse karna → 0 = current month end, 1 = next month end.
  Fix: Yaad rakho: months parameter is OFFSET from current, 0 = same month.

💬 Interview Questions:

🔍 Definition: NETWORKDAYS counts working days between two dates (excludes weekends and specified holidays). WORKDAY returns a date X working days from a start date. Both auto-skip Saturday and Sunday. NETWORKDAYS.INTL and WORKDAY.INTL allow custom weekends.

🎯 Samjho Simple Bhasha Mein: Project timeline banana? Weekends aur holidays skip karke working days count karo — NETWORKDAYS. Kisi task ka deadline nikalna hai jo 20 working days mein complete hoga? WORKDAY. Sales targets, project management, leave calculations — sab mein use hota hai.

💡 Syntax:

=NETWORKDAYS(start_date, end_date, [holidays])
=WORKDAY(start_date, days, [holidays])

INTL versions (custom weekends):
=NETWORKDAYS.INTL(start, end, [weekend], [holidays])
=WORKDAY.INTL(start, days, [weekend], [holidays])

Weekend codes:
1 = Sat+Sun (default), 2 = Sun+Mon, 11 = Sun only, 12 = Mon only

💻 Real-World Examples:

// NETWORKDAYS — Working days count: =NETWORKDAYS("1-Mar-2024", "31-Mar-2024") // 21 working days in March 2024
// Working days worked by employee:
=NETWORKDAYS(F2, TODAY())
// Rahul: Total working days
from
join to today

// With holidays list (A1:A5):
// Holidays in A1:A5: 26-Jan, 15-Aug, 2-Oct, 25-Dec, 1-Jan
=NETWORKDAYS(F2, TODAY(), $A$1:$A$5)
// Working days minus weekends AND holidays

// WORKDAY — Future working date:
=WORKDAY(TODAY(), 10)
// 10 working days
from today

=WORKDAY(TODAY(), 30, $A$1:$A$5)
// 30 working days later, skipping holidays

// Project deadline calculation:
=WORKDAY("1-Mar-2024", 20)
// Project takes 20 working days
// Result: 29-Mar-2024

// Days late (overdue):
=NETWORKDAYS(deadline, TODAY())
// If negative = still time left
// If positive = days overdue

// Custom weekend (Friday only weekend):
=NETWORKDAYS.INTL(start, end, 16)
// 16 = Friday only weekend

// Custom weekend (Friday + Saturday):
=NETWORKDAYS.INTL(start, end, 7)
// 7 = Fri+Sat (used in Middle East)

// Working days in current month:
=NETWORKDAYS(EOMONTH(TODAY(),-1)+1, EOMONTH(TODAY(),0))

📊 Weekend Codes for INTL versions:

⚠️ Common Mistakes:

• Mistake: Holidays parameter mein individual cells dena → Error!
  Fix: Range do: A1:A5 (holiday list). Absolute references use karo: $A$1:$A$5.
• Mistake: NETWORKDAYS end date included samajhna → Actually included hai!
  Fix: Same day start-end = 1 working day (if not weekend).
• Mistake: INTL version ke bina custom weekend chahiye → Basic version sirf Sat+Sun handle karta hai.
  Fix: Non-standard weekends ke liye NETWORKDAYS.INTL use karo.

💬 Interview Questions:

🔍 Definition: TEXT function converts a date into a formatted text string using custom format codes. Essential for displaying dates in specific formats within reports, combining dates with text, and creating custom date displays.

🎯 Samjho Simple Bhasha Mein: Date ko "15-Mar-2021" ki jagah "March 15, 2021" ya "Monday, 15 March" ya "Q1 2021" dikhana hai? TEXT function mein format code do — kuch bhi format bana sakte ho! Reports mein professional dates dikhane ke liye essential. Date ko text bana ke concatenate karo bhi easily.

💡 Date Format Codes:

Day: d (1), dd (01), ddd (Mon), dddd (Monday)
Month: m (3), mm (03), mmm (Mar), mmmm (March)
Year: yy (21), yyyy (2021)
Time: h, hh, m, mm, s, ss, AM/PM

💻 Real-World Examples:

// Assume F2 = 15-Mar-2021 (Monday)

// Standard formats:
=TEXT(F2, "dd/mm/yyyy") 
// "15/03/2021"
=TEXT(F2, "dd-mmm-yyyy") 
// "15-Mar-2021"
=TEXT(F2, "mmmm dd, yyyy") 
// "March 15, 2021"
=TEXT(F2, "dd/mm/yy") 
// "15/03/21"

// Day-related:
=TEXT(F2, "dddd") 
// "Monday"
=TEXT(F2, "ddd") 
// "Mon"
=TEXT(F2, "dddd, dd mmmm yyyy") 
// "Monday, 15 March 2021"

// Month name only:
=TEXT(F2, "mmmm") 
// "March"
=TEXT(F2, "mmm-yy") 
// "Mar-21"

// Custom combinations:
="Joined on " & TEXT(F2, "dd/mm/yyyy")

// "Joined on 15/03/2021"

=B2 & " joined on " & TEXT(F2, "dddd, mmmm dd, yyyy")

// "Rahul joined on Monday, March 15, 2021"

// Time formats:
=TEXT(NOW(), "hh:mm AM/PM") 
// "02:30 PM"
=TEXT(NOW(), "hh:mm:ss") 
// "14:30:25"

// Date + Time:
=TEXT(NOW(), "dd-mmm-yyyy hh:mm AM/PM")

// "15-Mar-2024 02:30 PM"

// Fiscal year (starts April):
=IF(MONTH(F2)>=4, "FY" & YEAR(F2) & "-" & RIGHT(YEAR(F2)+1,2),
"FY" & (YEAR(F2)-1) & "-" & RIGHT(YEAR(F2),2))

// Rahul (Mar 2021): FY2020-21

// Quarter display:
="Q" & ROUNDUP(MONTH(F2)/3, 0) & " " & YEAR(F2)

// "Q1 2021"

// Age in format "X years Y months":
=DATEDIF(F2,TODAY(),"Y")&"Y "&DATEDIF(F2,TODAY(),"YM")&"M"

// "3Y 0M"

📊 Date Format Cheat Sheet:

⚠️ Common Mistakes:

• Mistake: "M" vs "m" confusion → Both are month in date context, but in time context "m" = minutes!
  Fix: Time mein "hh:mm" — minutes. Date mein "mm" — month. Context matters.
• Mistake: TEXT result pe date functions apply karna → Result text hai!
  Fix: TEXT ka result display ke liye hai — date calculations ke liye original date use karo.

💬 Interview Questions:

🎯 Common Date Calculations — Cheat Sheet

Age in years: =DATEDIF(birthday, TODAY(), "Y")

Days since: =TODAY() - past_date

Current month end: =EOMONTH(TODAY(), 0)

First day of month: =EOMONTH(TODAY(),-1)+1

1 year from date: =EDATE(date, 12)

Working days: =NETWORKDAYS(start, end, holidays)

Weekend check: =WEEKDAY(A1, 2) > 5

Day name: =TEXT(A1, "dddd")

Next: Data Insights Excel Masterclass — Part 6

Part 6 mein hum cover karenge: Math & Statistical Functions — ROUND, CEILING, FLOOR, MOD, INT, ABS, LARGE, SMALL, RANK, MEDIAN, MODE, STDEV, PERCENTILE, QUARTILE, FREQUENCY — Excel ki mathematical power Data Insights par.

Happy Learning & Keep Excelling! 🚀