Date & Time Functions — Complete Mastery Guide

Part 4 mein humne Text Functions master kiye. Ab Excel ki Date & Time Functions sikhenge — TODAY, NOW, DATE, YEAR, MONTH, DAY, DATEDIF, WEEKDAY, WEEKNUM, EOMONTH, EDATE, NETWORKDAYS, WORKDAY, aur TEXT with Dates. HR reports, salary calculations, project tracking, age calculation — sab kuch dates ke bina incomplete hai. Real-world Excel mein Date Functions sabse zyada use hone wali functions mein se hain — aur interviews mein guaranteed poochi jaati hain!

📑 Is Part Mein Aap Kya Sikhenge:

Excel Date & Time functions ka complete arsenal — har function deep theory + real examples ke saath:

• TODAY & NOW: Current date aur date-time — dynamic formulas ka base
• DATE, YEAR, MONTH, DAY: Date construct karna aur date ke parts extract karna
• DATEDIF: Two dates ke beech ka difference — Age, Tenure, Duration calculation
• WEEKDAY & WEEKNUM: Day of week aur Week number nikalna
• EOMONTH & EDATE: Month-end dates aur future/past dates calculate karna
• NETWORKDAYS & WORKDAY: Working days count karna (weekends & holidays skip)
• TEXT with Dates: Dates ko custom formatted text mein convert karna

Is Part ke saare examples neeche diye gaye Employee data par based hain. Yeh data apni Excel sheet mein bana lo — saare formulas isi data pe practice karenge:

🔍 Definition (TODAY): The TODAY() function returns the current system date (date only, no time component). It is a volatile function — it recalculates automatically every time the workbook is opened or any cell is recalculated. It takes no arguments. Returns a serial number that Excel interprets as a date.

🔍 Definition (NOW): The NOW() function returns the current system date AND time (date + time component). Like TODAY(), it is volatile and recalculates on every workbook open or recalculation. It also takes no arguments. The difference is that NOW() includes the time portion (hours, minutes, seconds) while TODAY() does not.

🎯 Samjho Hinglish Mein: TODAY() matlab "aaj ki date batao" — sirf date, bina time ke. NOW() matlab "aaj ki date AUR abhi ka exact time batao." Yeh dono dynamic hain — kal file open karoge toh kal ki date dikhayenge, parso open karoge toh parso ki. Fixed nahi hain! Employee ki age calculate karni ho toh TODAY() use karo — =TODAY()-C2 se aaj tak ke din mil jayenge. Report mein timestamp lagana ho toh NOW() use karo — "Report generated at 15-Jun-2025 3:45 PM."

💡 Key Points:
• TODAY() Syntax: =TODAY() — No arguments, returns current date only
• NOW() Syntax: =NOW() — No arguments, returns current date + time
• Volatile: Dono har recalculation par update hote hain — file open karo, F9 dabao, koi cell edit karo — date/time refresh ho jayega
• Serial Number: Excel dates internally numbers hain — 1-Jan-1900 = 1, 2-Jan-1900 = 2, ..., 15-Jun-2025 = 45822. Time decimal mein store hota hai (0.5 = 12:00 PM noon)
• TODAY() = INT(NOW()): NOW() se time remove karo toh TODAY() mil jaata hai

💻 Formula Examples:

 // ═══════════════════════════════════════════ // TODAY() — Current Date // ═══════════════════════════════════════════
=TODAY()
// Result: 15-Jun-2025 (aaj ki date)

// ═══════════════════════════════════════════
// NOW() — Current Date + Time
// ═══════════════════════════════════════════

=NOW()
// Result: 15-Jun-2025 3:45:30 PM

// ═══════════════════════════════════════════
// Example 1: Days since joining (Amit - E001)
// ═══════════════════════════════════════════

=TODAY()-D2
// D2 = 10-Jan-2018 (Amit's Joining Date)
// Result: 2713 days (15-Jun-2025 minus 10-Jan-2018)
// Note: Result in days — format cell as Number, not Date

// ═══════════════════════════════════════════
// Example 2: Employee Age in Years (Amit)
// ═══════════════════════════════════════════

=INT((TODAY()-C2)/365.25)
// C2 = 15-Mar-1990 (Amit's DOB)
// (15-Jun-2025 - 15-Mar-1990) = 12876 days
// 12876 / 365.25 = 35.25 → INT = 35 years
// 365.25 accounts for leap years

// ═══════════════════════════════════════════
// Example 3: Days remaining until Project Deadline
// ═══════════════════════════════════════════

=G2-TODAY()
// G2 = 30-Sep-2025 (Amit's Project Deadline)
// Result: 107 days remaining
// Negative result = deadline passed!

// ═══════════════════════════════════════════
// Example 4: Is deadline passed? (Conditional)
// ═══════════════════════════════════════════

=IF(G2
// Checks if deadline is before today
// Kavita (E008): 10-Jun-2025 
// Amit (E001): 30-Sep-2025 

// ═══════════════════════════════════════════
// Example 5: Tenure in Years and Months (Text)
// ═══════════════════════════════════════════

=INT((TODAY()-D2)/365.25) & " years " & INT(MOD((TODAY()-D2),365.25)/30.44) & " months"
// D2 = 10-Jan-2018 (Amit)
// Result: "7 years 5 months"

// ═══════════════════════════════════════════
// Example 6: Extract only Time from NOW()
// ═══════════════════════════════════════════

=NOW()-TODAY()
// Returns time portion only (format as Time)
// Result: 3:45:30 PM

📊 Expected Results (assuming today = 15-Jun-2025):

⚠️ Common Mistakes:

• Mistake: TODAY()-D2 ka result 45822 jaisa number dikha raha hai date ki jagah. Fix: Cell ko Number format mein change karo (Right-click → Format Cells → Number). Result days mein hai — yeh date nahi hai, number hai.
• Mistake: Age calculate karte waqt 365 se divide karna — leap year miss hota hai. Fix: 365.25 se divide karo ya better DATEDIF function use karo (Topic 3 mein detail se aayega).
• Mistake: TODAY() ko static samajhna — "maine kal formula lagaya tha, kal ki date dikhani chahiye." Fix: TODAY() volatile hai — hamesha CURRENT date dikhata hai. Static date chahiye toh Ctrl+; (semicolon) se manually enter karo.
• Mistake: NOW() use karna jab sirf date chahiye — date difference mein decimal values aati hain. Fix: Date calculations ke liye hamesha TODAY() use karo, NOW() sirf timestamp ke liye.

💬 Interview Questions:

🔍 Definition (DATE): The DATE function creates a valid date from individual year, month, and day components. Syntax: =DATE(year, month, day). It returns a serial number representing the specified date. Useful when year, month, day are in separate columns or when constructing dates dynamically.

🔍 Definition (YEAR, MONTH, DAY): These three functions EXTRACT individual components from a date. =YEAR(date) returns the 4-digit year. =MONTH(date) returns the month number (1-12). =DAY(date) returns the day number (1-31). They are the opposite of DATE — DATE combines components into a date, these three SPLIT a date into components.

🎯 Samjho Hinglish Mein: DATE function ek date BANATA hai — tumhare paas alag alag columns mein year, month, day hain toh DATE(A1,B1,C1) se proper date ban jayegi. YEAR, MONTH, DAY ulta kaam karte hain — ek complete date se saal, mahina, ya din nikaal lete hain. Jaise ek employee ka DOB 15-Mar-1990 hai — YEAR(C2) = 1990, MONTH(C2) = 3, DAY(C2) = 15. Payroll mein month-wise grouping karna ho, fiscal year calculate karna ho, ya date ke parts se naya date banana ho — tab yeh functions kaam aate hain.

💡 Key Points:
• DATE(year, month, day): Creates a date. DATE(2025,6,15) = 15-Jun-2025
• YEAR(date): Extracts year (4-digit). YEAR("15-Jun-2025") = 2025
• MONTH(date): Extracts month (1-12). MONTH("15-Jun-2025") = 6
• DAY(date): Extracts day (1-31). DAY("15-Jun-2025") = 15
• DATE Smart Behavior: DATE(2025,13,1) = 1-Jan-2026 (auto-rolls over). DATE(2025,0,1) = 1-Dec-2024 (rolls back). Very useful for dynamic month calculations!
• Combination Power: DATE + YEAR/MONTH/DAY = date manipulation ka foundation

💻 Formula Examples:

// ═══════════════════════════════════════════ // DATE() — Create a Date
from Components // ═══════════════════════════════════════════
=DATE(2025, 6, 15)
// Result: 15-Jun-2025
// Creates a proper Excel date
from year, month, day

=DATE(2025, 13, 1)
// Result: 01-Jan-2026 (Month 13 = Jan of next year!)
// Excel auto-rolls: 12+1 = next year Jan

=DATE(2025, 0, 1)
// Result: 01-Dec-2024 (Month 0 = Dec of previous year!)
// Useful for going back months dynamically

=DATE(2025, -1, 1)
// Result: 01-Nov-2024 (Month -1 = Nov of previous year!)

// ═══════════════════════════════════════════
// YEAR(), MONTH(), DAY() — Extract Parts
// ═══════════════════════════════════════════

// Amit (E001) — DOB: 15-Mar-1990 (Cell C2)

=YEAR(C2)
// Result: 1990 (Birth year)

=MONTH(C2)
// Result: 3 (March = month 3)

=DAY(C2)
// Result: 15 (Day of the month)

// ═══════════════════════════════════════════
// Example 1: Extract Joining Year for all employees
// ═══════════════════════════════════════════

=YEAR(D2)
// D2 = 10-Jan-2018 → Result: 2018
// Drag down for all employees

// ═══════════════════════════════════════════
// Example 2: Extract Joining Month Name
// ═══════════════════════════════════════════

=TEXT(D2,"MMMM")
// D2 = 10-Jan-2018 → Result: "January"
// (TEXT with dates is covered in Topic 7 in detail)

// ═══════════════════════════════════════════
// Example 3: Find Fiscal Year (Apr-Mar)
// ═══════════════════════════════════════════

=IF(MONTH(D2)>=4, YEAR(D2)&"-"&YEAR(D2)+1, YEAR(D2)-1&"-"&YEAR(D2))
// D2 = 10-Jan-2018 → Month=1 (
// → 2017-2018 (Jan falls in FY 2017-18)
// D3 = 05-Apr-2016 → Month=4 (>=4)
// → 2016-2017 (Apr falls in FY 2016-17)

// ═══════════════════════════════════════════
// Example 4: Create First Day of Joining Month
// ═══════════════════════════════════════════

=DATE(YEAR(D2), MONTH(D2), 1)
// D2 = 10-Jan-2018 → DATE(2018, 1, 1) = 01-Jan-2018
// Gets first day of the month — useful for month grouping

// ═══════════════════════════════════════════
// Example 5: Create Last Day of Joining Month
// ═══════════════════════════════════════════

=DATE(YEAR(D2), MONTH(D2)+1, 0)
// D2 = 10-Jan-2018
// DATE(2018, 2, 0) = Day 0 of Feb = Last day of Jan
// Result: 31-Jan-2018
// This trick works for ANY month — even Feb/leap years!

// ═══════════════════════════════════════════
// Example 6: Employee's Birthday this year
// ═══════════════════════════════════════════

=DATE(YEAR(TODAY()), MONTH(C2), DAY(C2))
// C2 = 15-Mar-1990 (Amit's DOB)
// DATE(2025, 3, 15) = 15-Mar-2025 (birthday this year)
// Compare with TODAY() to check if birthday passed or upcoming

// ═══════════════════════════════════════════
// Example 7: Has birthday passed this year?
// ═══════════════════════════════════════════

=IF(DATE(YEAR(TODAY()),MONTH(C2),DAY(C2))
// Amit: 15-Mar-2025
// Sneha: 30-Dec-2025

📊 Expected Results — YEAR, MONTH, DAY Extraction:

⚠️ Common Mistakes:

• Mistake: YEAR(C2) karne par 1900 ya error aana — cell mein date text format mein hai. Fix: Pehle ensure karo ki date actual date format mein hai (right-aligned). Left-aligned hai toh text hai — DATEVALUE(C2) se convert karo ya cell re-format karo.
• Mistake: DATE function mein month 12 se zyada dena aur confuse hona. Fix: Yeh feature hai, bug nahi! DATE(2025,13,1) = Jan 2026. Yeh dynamic month calculation mein bahut useful hai.
• Mistake: Fiscal Year calculation mein month boundary galat rakhna — April ke baad FY change hota hai, March tak nahi. Fix: Indian FY ke liye MONTH >= 4 check karo (April = 4). Month 1,2,3 = previous FY, Month 4-12 = current FY.

💬 Interview Questions:

🔍 Definition: DATEDIF calculates the difference between two dates in specified units — Years, Months, or Days. Syntax: =DATEDIF(start_date, end_date, unit). The unit parameter can be "Y" (complete years), "M" (complete months), "D" (total days), "YM" (months excluding years), "MD" (days excluding months & years), "YD" (days excluding years). This is one of Excel's most powerful date functions but it is UNDOCUMENTED — it does not appear in Excel's formula autocomplete or help. It exists for compatibility with Lotus 1-2-3.

🎯 Samjho Hinglish Mein: HR department mein sabse common sawaal: "Employee ki age kya hai?" ya "Kitne saal se company mein hai?" DATEDIF yehi karta hai — do dates ke beech ka exact difference batata hai years, months, ya days mein. Sabse khaas baat — yeh COMPLETE units deta hai. Agar koi 2 saal 8 mahine se kaam kar raha hai, toh "Y" = 2 (complete years), "M" = 32 (complete months), "YM" = 8 (remaining months after removing years). Yeh function Excel mein type karte waqt suggest nahi hota — manually likhna padta hai — but works perfectly!

💡 DATEDIF Units Explained:
• "Y": Complete years between dates. DATEDIF("01-Jan-2020","15-Jun-2025","Y") = 5
• "M": Complete months between dates. DATEDIF("01-Jan-2020","15-Jun-2025","M") = 65
• "D": Total days between dates. DATEDIF("01-Jan-2020","15-Jun-2025","D") = 1992
• "YM": Months remaining AFTER removing complete years. = 5 (65 months - 5×12 = 5)
• "MD": Days remaining AFTER removing complete months. = 14
• "YD": Days between dates as if same year. = 165
• Rule: start_date MUST be <= end_date, otherwise #NUM! error

💻 Formula Examples:

 // ═══════════════════════════════════════════ // DATEDIF Syntax: // =DATEDIF(start_date, end_date, unit) // ═══════════════════════════════════════════
// ═══════════════════════════════════════════
// Example 1: Employee Age in Complete Years
// ═══════════════════════════════════════════

=DATEDIF(C2, TODAY(), "Y")
// C2 = 15-Mar-1990 (Amit's DOB)
// TODAY() = 15-Jun-2025
// Result: 35 (complete years)
// Much more accurate than (TODAY()-C2)/365.25!

// ═══════════════════════════════════════════
// Example 2: Tenure in Complete Months
// ═══════════════════════════════════════════

=DATEDIF(D2, TODAY(), "M")
// D2 = 10-Jan-2018 (Amit's Joining Date)
// Result: 89 (complete months of service)

// ═══════════════════════════════════════════
// Example 3: Tenure in Total Days
// ═══════════════════════════════════════════

=DATEDIF(D2, TODAY(), "D")
// D2 = 10-Jan-2018
// Result: 2713 (total days — same as TODAY()-D2)

// ═══════════════════════════════════════════
// Example 4: ⭐ Age as "X Years Y Months Z Days"
// (MOST ASKED in interviews!)
// ═══════════════════════════════════════════

=DATEDIF(C2,TODAY(),"Y") & " Years " & DATEDIF(C2,TODAY(),"YM") & " Months " & DATEDIF(C2,TODAY(),"MD") & " Days"
// C2 = 15-Mar-1990 (Amit)
// "Y" = 35 (complete years)
// "YM" = 3 (months after removing 35 years)
// "MD" = 0 (days after removing months)
// Result: "35 Years 3 Months 0 Days"

// ═══════════════════════════════════════════
// Example 5: ⭐ Tenure as "X Years Y Months"
// ═══════════════════════════════════════════

=DATEDIF(D2,TODAY(),"Y") & " Years " & DATEDIF(D2,TODAY(),"YM") & " Months"
// D2 = 10-Jan-2018 (Amit)
// "Y" = 7 (years), "YM" = 5 (months)
// Result: "7 Years 5 Months"

// ═══════════════════════════════════════════
// Example 6: Days until next birthday
// ═══════════════════════════════════════════

=DATEDIF(TODAY(), DATE(YEAR(TODAY())+IF(DATE(YEAR(TODAY()),MONTH(C2),DAY(C2))
// Finds next birthday date (this year or next year)
// Then calculates days from today to that date
// Amit (15-Mar): Birthday passed → next = 15-Mar-2026 → 273 days
// Priya (22-Jul): Birthday upcoming → 22-Jul-2025 → 37 days

// ═══════════════════════════════════════════
// Example 7: Probation check (6 months completed?)
// ═══════════════════════════════════════════

=IF(DATEDIF(D2,TODAY(),"M")>=6, "✅ Confirmed", "⏳ Probation")
// If tenure >= 6 months → Confirmed, else Probation
// All our sample employees have 6+ months → "✅ Confirmed"

📊 Expected Results — DATEDIF on Sample Data:

⚠️ Common Mistakes:

• Mistake: start_date end_date se BAAD mein hai — #NUM! error. Fix: DATEDIF mein start_date hamesha end_date se pehle hona chahiye. Age ke liye DATEDIF(DOB, TODAY(), "Y") — DOB pehle, TODAY() baad mein.
• Mistake: "MD" unit use karna — kabhi kabhi incorrect results deta hai (known bug in some Excel versions). Fix: "MD" ke results cross-verify karo. Alternative: =DAY(TODAY())-DAY(C2) se days ka difference nikalo (but yeh bhi tricky hai negative values ke liye).
• Mistake: DATEDIF autocomplete mein nahi dikh raha — sochna ki function exist nahi karta. Fix: Manually type karo — yeh undocumented hai but har version mein kaam karta hai. Spelling exact honi chahiye: DATEDIF (not DATEIF, not DATEDIFF).
• Mistake: Unit mein quotes bhoolna — "Y", "M", "D" mein quotes mandatory hain. Fix: Hamesha double quotes mein unit likho: =DATEDIF(C2, TODAY(), "Y")

💬 Interview Questions:

🔍 Definition (WEEKDAY): WEEKDAY returns the day of the week for a given date as a number (1-7). Syntax: =WEEKDAY(date, [return_type]). The return_type parameter controls which day is considered day 1. Default (type 1): Sunday=1, Monday=2, ..., Saturday=7. Type 2: Monday=1, ..., Sunday=7. Type 3: Monday=0, ..., Sunday=6.

🔍 Definition (WEEKNUM): WEEKNUM returns the week number of the year (1-54) for a given date. Syntax: =WEEKNUM(date, [return_type]). Default (type 1): Week starts on Sunday. Type 2: Week starts on Monday. Type 21: ISO 8601 standard (Monday start, week containing Jan 4 = Week 1).

🎯 Samjho Hinglish Mein: WEEKDAY = "yeh date kiS din padi thi?" — Sunday, Monday, ya koi aur. Iska result number mein aata hai (1-7). Tumhe employee ki joining date ka day of week jaanna ho — WEEKDAY(D2) se pata chalega ki Monday tha ya Friday. WEEKNUM = "yeh date saal ke kis week mein aati hai?" — project planning mein "Week 24" type tracking ke liye use hota hai. HR mein "is employee ne week 25 mein joining ki" type analysis ke liye useful.

💡 WEEKDAY Return Types:
• Type 1 (Default): Sunday=1, Monday=2, Tuesday=3, Wednesday=4, Thursday=5, Friday=6, Saturday=7
• Type 2: Monday=1, Tuesday=2, Wednesday=3, Thursday=4, Friday=5, Saturday=6, Sunday=7
• Type 3: Monday=0, Tuesday=1, Wednesday=2, Thursday=3, Friday=4, Saturday=5, Sunday=6
• Most Used: Type 2 (Monday=1) — India mein week Monday se start hota hai

💻 Formula Examples:

 // ═══════════════════════════════════════════ // WEEKDAY — Day of the Week // ═══════════════════════════════════════════
// Example 1: Joining day number (Default: Sunday=1)
=WEEKDAY(D2)
// D2 = 10-Jan-2018 (Wednesday)
// Result: 4 (Sunday=1, Mon=2, Tue=3, Wed=4)

// Example 2: Joining day number (Monday=1)
=WEEKDAY(D2, 2)
// D2 = 10-Jan-2018 (Wednesday)
// Result: 3 (Monday=1, Tue=2, Wed=3)

// ═══════════════════════════════════════════
// Example 3: Get Day NAME from Day Number
// ═══════════════════════════════════════════

=CHOOSE(WEEKDAY(D2), "Sun","Mon","Tue","Wed","Thu","Fri","Sat")
// D2 = 10-Jan-2018 → WEEKDAY = 4 → CHOOSE picks 4th = "Wed"
// Result: "Wed"

// Easier alternative:
=TEXT(D2, "dddd")
// Result: "Wednesday" (full day name)

=TEXT(D2, "ddd")
// Result: "Wed" (short day name)

// ═══════════════════════════════════════════
// Example 4: Is Joining Date a Weekend?
// ═══════════════════════════════════════════

=IF(WEEKDAY(D2,2)>5, "Weekend 🏖️", "Weekday 💼")
// Type 2: Mon=1...Fri=5, Sat=6, Sun=7
// If >5 → Saturday or Sunday → Weekend
// D2 = 10-Jan-2018 (Wed) → WEEKDAY=3 → "Weekday 💼"

// ═══════════════════════════════════════════
// Example 5: Highlight if deadline falls on weekend
// ═══════════════════════════════════════════

=IF(WEEKDAY(G2,2)>5, "⚠️ Weekend Deadline!", "✅ Weekday")
// G2 = Project Deadline

// ═══════════════════════════════════════════
// WEEKNUM — Week Number of the Year
// ═══════════════════════════════════════════

// Example 6: Joining week number
=WEEKNUM(D2)
// D2 = 10-Jan-2018 → Result: 2 (Week 2 of 2018)

=WEEKNUM(D2, 2)
// Type 2: Week starts on Monday
// Result: 2

// ═══════════════════════════════════════════
// Example 7: ISO Week Number (International Standard)
// ═══════════════════════════════════════════

=ISOWEEKNUM(D2)
// Returns ISO 8601 week number
// D2 = 10-Jan-2018 → Result: 2
// Available in Excel 2013+

// ═══════════════════════════════════════════
// Example 8: "Week X of Year Y" format
// ═══════════════════════════════════════════

="Week " & WEEKNUM(D2,2) & " of " & YEAR(D2)
// D2 = 10-Jan-2018 → "Week 2 of 2018"

📊 Expected Results — WEEKDAY & WEEKNUM:

⚠️ Common Mistakes:

• Mistake: WEEKDAY ka return type bhoolna — default Sunday=1, India mein Monday=1 chahiye. Fix: Hamesha second argument 2 do: =WEEKDAY(D2, 2) — Monday=1 start.
• Mistake: WEEKDAY result se day name nahi pata — 3 ka matlab Wednesday yaad rakhna padta hai. Fix: =TEXT(D2, "dddd") directly day name deta hai — simple aur clean.
• Mistake: WEEKNUM aur ISOWEEKNUM confuse karna — dono alag standards hain. Fix: Indian companies mein usually WEEKNUM(date, 2) kaafi hai. International reporting ke liye ISOWEEKNUM use karo.

💬 Interview Questions:

🔍 Definition (EOMONTH): EOMONTH returns the last day of the month that is a specified number of months before or after the start date. Syntax: =EOMONTH(start_date, months). Months=0 gives end of current month, Months=1 gives end of next month, Months=-1 gives end of previous month. "EOMONTH" stands for End Of Month.

🔍 Definition (EDATE): EDATE returns a date that is a specified number of months before or after the start date, on the SAME DAY of the month. Syntax: =EDATE(start_date, months). If the starting day doesn't exist in the target month (e.g., 31st in a 30-day month), it returns the last day of that month.

🎯 Samjho Hinglish Mein: EOMONTH = "Mahine ka last din batao." Salary processing mein month-end date chahiye? EOMONTH(D2, 0) = current month ka last day. Invoice due date next month end hai? EOMONTH(D2, 1) = next month ka last day. EDATE = "X mahine baad SAME day ki date batao." Probation 6 month hai? EDATE(joining_date, 6) = exactly 6 months baad ki date. Insurance renew 12 months baad? EDATE(start, 12). Dono HR, Finance, aur Project Management mein bahut use hote hain.

💡 Key Points:
• EOMONTH(date, 0): Last day of same month
• EOMONTH(date, 1): Last day of next month
• EOMONTH(date, -1): Last day of previous month
• EDATE(date, 6): Same day, 6 months later
• EDATE(date, -3): Same day, 3 months earlier
• EDATE Smart Behavior: EDATE("31-Jan-2025", 1) = 28-Feb-2025 (Feb has no 31st, returns last day)
• Tip: EOMONTH(date, 0)+1 = First day of NEXT month!

💻 Formula Examples:

 // ═══════════════════════════════════════════ // EOMONTH — End of Month // ═══════════════════════════════════════════
// Example 1: Last day of Joining Month
=EOMONTH(D2, 0)
// D2 = 10-Jan-2018 → Result: 31-Jan-2018
// Months = 0 → same month ka end

// Example 2: Last day of NEXT month from Joining
=EOMONTH(D2, 1)
// D2 = 10-Jan-2018 → Result: 28-Feb-2018
// Months = 1 → next month ka end (Feb 2018 = 28 days)

// Example 3: Last day of PREVIOUS month
=EOMONTH(D2, -1)
// D2 = 10-Jan-2018 → Result: 31-Dec-2017

// Example 4: Last day of current month (dynamic)
=EOMONTH(TODAY(), 0)
// If today = 15-Jun-2025 → Result: 30-Jun-2025

// Example 5: First day of NEXT month
=EOMONTH(D2, 0)+1
// D2 = 10-Jan-2018 → 31-Jan-2018 + 1 = 01-Feb-2018
// Trick: End of current month + 1 day = Start of next month

// Example 6: First day of current month
=EOMONTH(D2, -1)+1
// D2 = 10-Jan-2018 → 31-Dec-2017 + 1 = 01-Jan-2018
// End of previous month + 1 = Start of current month

// ═══════════════════════════════════════════
// EDATE — Exact Months Away (Same Day)
// ═══════════════════════════════════════════

// Example 7: Probation End Date (6 months after joining)
=EDATE(D2, 6)
// D2 = 10-Jan-2018 → Result: 10-Jul-2018
// Exactly 6 months from joining, same day (10th)

// Example 8: Contract Renewal (12 months after joining)
=EDATE(D2, 12)
// D2 = 10-Jan-2018 → Result: 10-Jan-2019
// Exactly 1 year from joining

// Example 9: 3 months BEFORE deadline
=EDATE(G2, -3)
// G2 = 30-Sep-2025 → Result: 30-Jun-2025
// Warning date: 3 months before deadline

// Example 10: Smart Day Handling
=EDATE("31-Jan-2025", 1)
// Feb has no 31st → Result: 28-Feb-2025
// EDATE automatically adjusts to last valid day!

// ═══════════════════════════════════════════
// Example 11: Salary review date every 6 months
// ═══════════════════════════════════════════

=EDATE(D2, CEILING(DATEDIF(D2,TODAY(),"M")/6, 1)*6)
// Finds next 6-month review date from joining
// Amit joined 10-Jan-2018, reviews at 10-Jul-2018,
// 10-Jan-2019, ... next = 10-Jul-2025

// ═══════════════════════════════════════════
// Example 12: Has probation ended?
// ═══════════════════════════════════════════

=IF(TODAY()>=EDATE(D2,6), "✅ Confirmed", "⏳ In Probation")
// Checks if 6 months from joining has passed

📊 Expected Results — EOMONTH & EDATE:

⚠️ Common Mistakes:

• Mistake: EOMONTH result ko serial number ke roop mein dekhna (45838 jaisa). Fix: Cell ko Date format mein change karo — Right-click → Format Cells → Date. EOMONTH date serial number return karta hai.
• Mistake: EDATE mein 31-Jan se 1 month add karna aur 31-Feb expect karna. Fix: EDATE automatically adjust karta hai — 31-Jan + 1 month = 28-Feb (ya 29-Feb leap year mein). Yeh feature hai, bug nahi.
• Mistake: EOMONTH aur EDATE confuse karna. Fix: EOMONTH = month ka LAST DAY. EDATE = SAME DAY, different month. EOMONTH(10-Jan, 1) = 28-Feb. EDATE(10-Jan, 1) = 10-Feb. Bilkul different!

💬 Interview Questions:

🔍 Definition (NETWORKDAYS): NETWORKDAYS calculates the number of working days (excluding weekends and optionally holidays) between two dates. Syntax: =NETWORKDAYS(start_date, end_date, [holidays]). By default, it excludes Saturdays and Sundays. The holidays parameter accepts a range of dates that should also be excluded (public holidays, company-specific holidays). Returns a whole number.

🔍 Definition (WORKDAY): WORKDAY returns a date that is a specified number of working days before or after the start date, skipping weekends and optional holidays. Syntax: =WORKDAY(start_date, days, [holidays]). If days is positive, it counts forward. If negative, it counts backward. Useful for calculating deadlines: "30 working days se project complete hona chahiye — kab hoga?"

🎯 Samjho Hinglish Mein: NETWORKDAYS = "Do dates ke beech kitne KAAM KE DIN hain?" — sirf Monday-Friday count karta hai, Saturday-Sunday skip. Holidays ki list doge toh woh bhi skip. Project mein "kitne working days bache hain deadline tak?" — NETWORKDAYS(TODAY(), deadline, holidays). WORKDAY = ulta — "X working days baad kaunsi date aayegi?" — "Agar 20 working days mein deliver karna hai toh kab tak milega?" = WORKDAY(TODAY(), 20, holidays). Dono HR, Payroll, Project Management mein essential hain.

💡 Key Points:
• NETWORKDAYS: Counts working days between two dates (Mon-Fri). Returns NUMBER.
• WORKDAY: Adds working days to a date (Mon-Fri). Returns DATE.
• Holidays parameter: Optional — ek range specify karo jismein holiday dates hain. Dono functions un dates ko bhi skip karenge.
• INTL variants: NETWORKDAYS.INTL aur WORKDAY.INTL — custom weekends define kar sakte ho (e.g., Friday-Saturday weekend for Middle East).
• Negative days in WORKDAY: Peeche count karta hai — "10 working days PEHLE ki date."

💻 Formula Examples:

// ═══════════════════════════════════════════ // Holiday List Setup (Put in cells I2:I6) // ═══════════════════════════════════════════ // I2: 26-Jan-2025 (Republic Day) // I3: 15-Aug-2025 (Independence Day) // I4: 02-Oct-2025 (Gandhi Jayanti) // I5: 01-Nov-2025 (Diwali) // I6: 25-Dec-2025 (Christmas)
// ═══════════════════════════════════════════
// NETWORKDAYS — Count Working Days
// ═══════════════════════════════════════════

// Example 1: Working days between Joining and Today
=NETWORKDAYS(D2, TODAY())
// D2 = 10-Jan-2018, TODAY() = 15-Jun-2025
// Result: 1938 working days (excludes Sat & Sun)
// Compare with DATEDIF(D2,TODAY(),"D") = 2713 total days

// Example 2: Working days to deadline (no holidays)
=NETWORKDAYS(TODAY(), G2)
// G2 = 30-Sep-2025 → Result: ~77 working days

// Example 3: Working days to deadline (WITH holidays)
=NETWORKDAYS(TODAY(), G2, $I$2:$I$6)
// Same as above but also excludes 5 public holidays
// Result: ~75 working days (2 holidays fall in range)
// $I$2:$I$6 = absolute reference for holiday list

// Example 4: Total working days in a month
=NETWORKDAYS(EOMONTH(TODAY(),-1)+1, EOMONTH(TODAY(),0))
// First day of current month to last day of current month
// Jun 2025 → 1-Jun to 30-Jun → 21 working days

// ═══════════════════════════════════════════
// WORKDAY — Add Working Days to Get Future Date
// ═══════════════════════════════════════════

// Example 5: Delivery date = 30 working days
from today
=WORKDAY(TODAY(), 30)
// TODAY() = 15-Jun-2025
// Result: 25-Jul-2025 (30 working days later)
// Skips all Sat & Sun automatically

// Example 6: Delivery date with holidays excluded
=WORKDAY(TODAY(), 30, $I$2:$I$6)
// Result: 28-Jul-2025 (holidays bhi skip)

// Example 7: Training completion = 10 working days
from joining
=WORKDAY(D2, 10)
// D2 = 10-Jan-2018 → Result: 24-Jan-2018
// 10 working days
from joining (skips weekends)

// Example 8: Go BACKWARD — 5 working days before deadline
=WORKDAY(G2, -5)
// G2 = 30-Sep-2025 → Result: 23-Sep-2025
// 5 working days before deadline = reminder date

// ═══════════════════════════════════════════
// NETWORKDAYS.INTL — Custom Weekends
// ═══════════════════════════════════════════

// Example 9: Only Sunday as weekend (Sat working)
=NETWORKDAYS.INTL(D2, TODAY(), 11)
// Weekend code 11 = Sunday only
// Counts Mon-Sat as working, only Sun off

// Common Weekend Codes:
// 1 = Sat-Sun (default)
// 2 = Sun-Mon
// 7 = Fri-Sat (Middle East)
// 11 = Sunday only
// 12 = Monday only

📊 Expected Results — NETWORKDAYS & WORKDAY:

⚠️ Common Mistakes:

• Mistake: Holidays list mein relative reference use karna — drag karne par shift ho jaata hai. Fix: Holiday range ko absolute reference banao: $I$2:$I$6. Ya Named Range banao: "Holidays" naam de do aur formula mein use karo.
• Mistake: NETWORKDAYS mein start aur end date ULTE dena — negative result aata hai. Fix: Start date pehle, end date baad mein. Negative result matlab deadline already pass ho gayi.
• Mistake: WORKDAY ka result serial number mein dikhai dena (45847). Fix: Cell ko Date format mein change karo. WORKDAY date serial number return karta hai.
• Mistake: NETWORKDAYS.INTL ka weekend code yaad na hona. Fix: Most common: 1=Sat-Sun (default), 11=Sunday only, 7=Fri-Sat. Cheat sheet bana lo.

💬 Interview Questions:

🔍 Definition: The TEXT function converts a date (or number) to text in a specified format. Syntax: =TEXT(value, format_text). When used with dates, it allows you to display dates in any custom format — full month names, day names, ordinal dates, custom separators, and more. The result is a TEXT string (not a date) — it cannot be used in date arithmetic. It is primarily used for display/reporting purposes.

🎯 Samjho Hinglish Mein: TEXT function dates ko sundar banata hai — tumhari marzi ka format. "15-Jun-2025" ko "Sunday, June 15, 2025" mein convert karna ho, ya sirf "Jun 2025" ya "Q2 2025" ya "2025/06" — sab TEXT function se hota hai. Report headers mein "Report Generated: June 15, 2025" likhna ho toh = "Report Generated: " & TEXT(TODAY(), "MMMM DD, YYYY"). Yaad rakho — TEXT ka result STRING hai, date nahi — isko aage date calculations mein use nahi kar sakte.

💡 Date Format Codes — Complete Reference:
• "d": Day without leading zero (5)
• "dd": Day with leading zero (05)
• "ddd": Short day name (Mon, Tue, Wed)
• "dddd": Full day name (Monday, Tuesday)
• "m": Month without leading zero (6)
• "mm": Month with leading zero (06)
• "mmm": Short month name (Jun)
• "mmmm": Full month name (June)
• "mmmmm": Single letter month (J) — useful for charts
• "yy": Two-digit year (25)
• "yyyy": Four-digit year (2025)
• "h", "hh": Hours (3, 03)
• "m", "mm": Minutes (when after h) (45, 45)
• "s", "ss": Seconds (30, 30)
• "AM/PM": 12-hour format indicator

💻 Formula Examples:

// ═══════════════════════════════════════════ // TEXT with Dates — All Format Examples // (Using D2 = 10-Jan-2018, today = 15-Jun-2025) // ═══════════════════════════════════════════
// Example 1: Day Name (Full)
=TEXT(D2, "dddd")
// Result: "Wednesday"

// Example 2: Day Name (Short)
=TEXT(D2, "ddd")
// Result: "Wed"

// Example 3: Month Name (Full)
=TEXT(D2, "mmmm")
// Result: "January"

// Example 4: Month Name (Short)
=TEXT(D2, "mmm")
// Result: "Jan"

// Example 5: "Month Year" format
=TEXT(D2, "mmmm yyyy")
// Result: "January 2018"

// Example 6: "DD-MMM-YYYY" format
=TEXT(D2, "dd-mmm-yyyy")
// Result: "10-Jan-2018"

// Example 7: "Full Date" format
=TEXT(D2, "dddd, mmmm dd, yyyy")
// Result: "Wednesday, January 10, 2018"

// Example 8: "YYYY/MM/DD" (ISO-like)
=TEXT(D2, "yyyy/mm/dd")
// Result: "2018/01/10"

// Example 9: "MMM-YY" for charts/headers
=TEXT(D2, "mmm-yy")
// Result: "Jan-18"

// ═══════════════════════════════════════════
// Real-World Use Cases
// ═══════════════════════════════════════════

// Example 10: Report Header with Date
="Report Generated: " & TEXT(NOW(), "dd-mmm-yyyy hh:mm AM/PM")
// Result: "Report Generated: 15-Jun-2025 03:45 PM"

// Example 11: Employee Welcome Message
="Welcome " & B2 & "! You joined on " & TEXT(D2, "dddd, mmmm dd, yyyy") & "."
// Result: "Welcome Amit Sharma! You joined on Wednesday, January 10, 2018."

// Example 12: Quarter
from Date
="Q" & INT((MONTH(D2)-1)/3)+1 & " " & YEAR(D2)
// D2 = 10-Jan-2018 → Month=1 → (1-1)/3=0 → INT=0 → +1=1
// Result: "Q1 2018"

// Example 13: "Joined in [Month] [Year]" column
="Joined in " & TEXT(D2, "mmmm yyyy")
// Result: "Joined in January 2018"

// Example 14: Time only
from NOW()
=TEXT(NOW(), "hh:mm:ss AM/PM")
// Result: "03:45:30 PM"

// Example 15: Day with ordinal suffix (1st, 2nd, 3rd)
=DAY(D2) & IF(OR(DAY(D2)={1,21,31}),"st",IF(OR(DAY(D2)={2,22}),"nd",IF(OR(DAY(D2)={3,23}),"rd","th"))) & TEXT(D2," mmmm yyyy")
// D2 = 10-Jan-2018 → "10th January 2018"

📊 TEXT Formats — Quick Reference Table:

⚠️ Common Mistakes:

• Mistake: TEXT function ke result ko date calculations mein use karna — error aata hai. Fix: TEXT() STRING return karta hai, DATE nahi. Arithmetic ke liye original date column use karo, display ke liye TEXT use karo. "15-Jun-2025" text hai, date nahi — isse subtract nahi kar sakte.
• Mistake: Month ke liye "m" use karna time ke context mein — minutes aa jaate hain. Fix: "m" date ke context mein month hai, lekin agar "h" (hours) ke baad aaye toh minutes ban jaata hai. "hh:mm" = hours:minutes. "mm/dd" = month/day. Context matter karta hai.
• Mistake: Format codes case-insensitive hain lekin convention — lowercase likhna better hai. Fix: "dd", "mm", "yyyy" lowercase mein likhein — consistent aur readable.

💬 Interview Questions:

Date & Time ke saare functions ek nazar mein:

🚀 Part 5 Complete — Date & Time Functions Mastered!

Ab tum Excel mein dates ke saath kuch bhi kar sakte ho — Age, Tenure, Deadlines, Working Days, Custom Formatting — sab covered! In functions ko practice karo sample data par — HR reports, Project trackers, Salary sheets mein ye functions har jagah use hote hain.