Tag: Excel Functions

Because time is money, and you’re currently losing both by not knowing these.

Let me paint you a picture.

It’s Monday morning. You have a spreadsheet. The spreadsheet has dates in it. Someone — let’s call them “Past You” — has entered those dates in seventeen different formats, across three different columns, using a mix of text strings, actual date values, and what appears to be a cry for help disguised as a number.

You need to calculate how many days until a project deadline. You need to extract the month. You need to figure out whether a delivery happened on a weekend. And you need all of this before the 9am standup, which is in four minutes.

This is the moment Excel’s date and time functions were born for. And yet — most people know maybe three of them.

First, A Two-Minute Explanation That Will Save You Hours

Before we dive in, here’s the thing most people never explain about dates in Excel: Excel doesn’t actually store dates as dates.

Seriously. Under the hood, every date is just a number. January 1, 1900 = 1. January 2, 1900 = 2. Today (April 16, 2026) = some number in the 46,000s. Excel just formats those numbers to look like dates, which is very considerate of it.

Why does this matter? Because once you understand that dates are numbers, everything about date functions suddenly makes logical sense. Adding 7 to a date gives you next week. Subtracting two dates gives you the number of days between them. The formula isn’t doing anything mystical — it’s just arithmetic with a nice coat of paint.

Times work the same way, but as decimal fractions of a day. Noon (12:00) = 0.5. 6am = 0.25. Midnight = 0. Got it? Good. Now let’s talk functions.

The “What Day/Time Is It Right Now?” Functions

TODAY()

What it does: Returns today’s date. Recalculates every time the file opens or refreshes.

Syntax: =TODAY()

Real-life example: You’re tracking a project deadline in B2 (let’s say it’s May 30, 2026). In C2 you put =B2-TODAY() and now you always know, instantly and without doing math in your head, exactly how many days you have left. Which might be motivating. Or terrifying. Either way, accurate.

💡 Pro Tip: TODAY() is “volatile” — it recalculates constantly. If you need a permanent timestamp of when something was entered, use Ctrl+; instead, which hard-codes the date and never changes.

NOW()

What it does: Returns the current date and time down to the minute. Also recalculates every time the sheet refreshes.

Syntax: =NOW()

Real-life example: You’re building a log of customer support tickets. Every time a new ticket is submitted, you want to record the exact timestamp. =NOW() in the timestamp column gives you something like “16/04/2026 09:47” — date and time, together, in one cell. Extremely useful when “when was this submitted?” is a question that leads to arguments.

💡 Pro Tip: NOW() returns a number with decimals. If you subtract NOW() from a future date and you get a weird decimal, that’s because it’s including the fractional time of day. Use INT(NOW()) to strip the time and get just today’s date as a whole number.

The “Build a Date From Parts” Functions

DATE(year, month, day)

What it does: Constructs a proper Excel date from three separate numbers — a year, a month, and a day. This is your rescue function when someone has helpfully stored the year in column A, the month in column B, and the day in column C.

Real-life example: You have an export from an ancient database system. Column A says “2026”, column B says “4”, column C says “16”. None of it is usable as a date yet. =DATE(A2,B2,C2) stitches them together into April 16, 2026 — a real, actual Excel date you can calculate with.

💡 Pro Tip: DATE() handles overflow beautifully. =DATE(2026,13,1) doesn’t give you an error — it gives you January 1, 2027, because month 13 just rolls into the next year. This is wildly useful for adding months to dates without weird edge cases.

TIME(hour, minute, second)

What it does: Same concept as DATE(), but for time. Constructs an Excel time value from separate hour, minute, and second components.

Real-life example: You’re managing shift schedules. In your system, start times come in as three separate columns: 9 (hour), 30 (minute), 0 (second). =TIME(A2,B2,C2) combines them into 9:30:00 AM — a real time value you can then use for duration calculations, comparisons, and scheduling logic.

💡 Pro Tip: Need to add 90 minutes to a time? Don’t add 90. Add TIME(0,90,0). Excel will handle the rollover correctly. Adding the raw number 90 will add 90 days, not minutes, and you’ll have a very confusing schedule.

The “Tear a Date Apart” Functions

YEAR(date)

What it does: Extracts just the year from a date. Returns a four-digit number.

Real-life example: You have a column of transaction dates going back five years and you want to create a pivot table by year. Add a helper column with =YEAR(A2) and suddenly you have a clean “Year” field to group on. Simple, extremely useful, used approximately nine million times a day in spreadsheets around the world.

MONTH(date)

What it does: Extracts the month number (1 through 12) from a date.

Real-life example: Your sales report needs a “Month” column for filtering. =MONTH(A2) gives you a number. Combine with =TEXT(A2,"MMMM") if you want the actual month name instead. Report looks good, boss is happy, you leave on time. This is the dream.

💡 Pro Tip: To convert a month number back to a name, use =TEXT(DATE(2000,A2,1),"MMMM"). Classic Excel party trick.

DAY(date)

What it does: Extracts the day of the month (1 through 31) from a date.

Real-life example: You need to flag all invoices issued on the last day of the month. =DAY(A2)=DAY(EOMONTH(A2,0)) — yes, we’ll get to EOMONTH shortly — checks whether the day of an invoice date equals the last day of its month. Month-end billing logic, solved.

HOUR(time), MINUTE(time), SECOND(time)

What they do: Extract the hour, minute, or second from a time value, respectively.

Real-life example (all three): You’re analyzing customer call durations. The start time is in A2, end time in B2, and the duration is in C2 as a time value. Now you need this in a readable format. =HOUR(C2)&"h "&MINUTE(C2)&"m "&SECOND(C2)&"s" turns 0:47:23 into “0h 47m 23s.” Your operations manager will use words like “professional” and “organised.” You will nod calmly.

The “What Day of the Week Is This?” Functions

WEEKDAY(date, [return_type])

What it does: Returns a number representing the day of the week for a given date. The return_type argument controls the numbering system (1=Sunday through Saturday, 2=Monday through Sunday, etc.). Default is 1.

Real-life example: You’re scheduling automated reports and need to make sure nothing sends on a weekend. =WEEKDAY(A2,2) returns 6 for Saturday and 7 for Sunday (Monday-based numbering). Wrap it in an IF: =IF(WEEKDAY(A2,2)>5,"Weekend","Weekday") and you’ve got an instant flag. No more reports going out on a Saturday to an inbox nobody checks.

💡 Pro Tip: Use return_type = 2 (Monday = 1, Sunday = 7) if you’re in Europe or working on business logic that treats Monday as the start of the week. This avoids a lot of off-by-one headaches.

WEEKNUM(date, [return_type])

What it does: Returns the week number of the year for a given date. Week 1 is the week containing January 1st. The return_type argument controls which day the week starts on.

Real-life example: You’re reporting on weekly sales performance and your stakeholders reference “Week 15” and “Week 28” instead of actual dates (because they’re creatures of habit and you’ve accepted this). =WEEKNUM(A2) gives you the ISO week number, which you can use to group, sort, and slice your data by week without ever writing a formula that involves seven nested IFs. Everyone who has written seven nested IFs to solve this problem knows the pain being avoided here.

ISOWEEKNUM(date)

What it does: Returns the ISO week number — the international standard used in Europe and most professional business contexts. ISO weeks always start on Monday and the first week of the year is the one containing the first Thursday.

Real-life example: You’re working with a European partner who refers to “KW16” (Kalenderwoche 16 — German for calendar week 16). =ISOWEEKNUM(A2) gets you on the same page without having to explain to your German colleague why your week numbers are off by one. Cross-border collaboration, sorted.

The “How Much Time Has Passed?” Functions

DATEDIF(start_date, end_date, unit)

What it does: Calculates the difference between two dates in a specified unit: “Y” for years, “M” for months, “D” for days, “YM” for months excluding years, “MD” for days excluding months, “YD” for days excluding years.

This function is technically undocumented by Microsoft. It exists, it works perfectly, but Microsoft didn’t include it in their official function list for mysterious reasons that nobody has ever fully explained. It’s the Excel equivalent of a secret room.

Real-life example: You’re calculating employee tenure for an HR report. =DATEDIF(A2,TODAY(),"Y") gives you complete years of service. Want to be precise? =DATEDIF(A2,TODAY(),"Y")&" years, "&DATEDIF(A2,TODAY(),"YM")&" months" gives you “3 years, 7 months.” The kind of thing that makes an HR system look polished and thorough. Very satisfying.

💡 Pro Tip: Always make sure end_date is greater than start_date. DATEDIF throws an error if the dates are the wrong way around and, unlike most Excel errors, it is not subtle about it.

DAYS(end_date, start_date)

What it does: Returns the number of days between two dates. Cleaner than subtraction for some scenarios.

Real-life example: Subscription renewal tracking. Column A has sign-up dates, column B has today’s date. =DAYS(B2,A2) gives you subscription age in days. When this hits 365, you know it’s renewal time. Simple, readable, does exactly one thing.

DAYS360(start_date, end_date, [method])

What it does: Calculates days between two dates based on a 360-day year (twelve 30-day months). This is a financial standard, not a mistake.

Real-life example: You’re working in accounting and calculating interest on a loan or bond using the 30/360 day count convention that the financial industry uses. =DAYS360(A2,B2) gives you the “banking days” between two dates. If someone in finance handed you a spreadsheet using this function and you thought it was a typo — it wasn’t.

💡 Pro Tip: The optional method argument is for US (FALSE) vs. European (TRUE) day count conventions. Yes, there are two. Yes, this matters in international financial documents. Yes, getting them mixed up is awkward.

NETWORKDAYS(start_date, end_date, [holidays])

What it does: Returns the number of working days (Monday–Friday) between two dates. You can optionally pass a range of holiday dates to exclude those too.

Real-life example: Project management gold. Client signs off on a project on April 16. The deliverable is due May 16. =NETWORKDAYS(A2,B2,HolidayRange) tells you how many actual working days you have, excluding weekends and any public holidays you’ve listed. This number will either reassure you or cause a mild crisis. Either outcome is useful information.

💡 Pro Tip: Build a dedicated “Holidays” named range in your workbook and reference it in every NETWORKDAYS formula. When holidays change next year, you update one list and every formula updates automatically. This is the kind of forward-thinking that makes colleagues ask if you took a course somewhere.

NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])

What it does: Same as NETWORKDAYS but lets you define which days are the “weekend.” Because not everyone works a Monday–Friday schedule, and Excel knows this.

Real-life example: You’re calculating delivery times for a logistics company where the drivers work Tuesday–Saturday and have Sunday–Monday off. =NETWORKDAYS.INTL(A2,B2,2,HolidayRange) with weekend code “2” means Monday and Tuesday are treated as non-working days. No more manually adjusting day counts because the formula assumed a Western workweek that your operations don’t follow.

💡 Pro Tip: The weekend argument accepts both number codes (1–7, 11–17) and a 7-character string like “1000001” where each character represents Mon–Sun and “1” means non-working day. The string method is more flexible for unusual schedules.

WORKDAY(start_date, days, [holidays])

What it does: Returns the date that is a certain number of working days after (or before, with a negative number) a start date. Skips weekends automatically.

Real-life example: An order is placed on April 16. Your SLA promises delivery within 5 business days. =WORKDAY(A2,5,HolidayRange) returns April 23 — which is the correct delivery deadline after skipping the weekend. Much better than telling a customer their order arrives “in 5 days” and then having them show up on Saturday to collect it.

WORKDAY.INTL(start_date, days, [weekend], [holidays])

What it does: WORKDAY with the same custom weekend flexibility as NETWORKDAYS.INTL.

Real-life example: Same logistics scenario as above, but now you’re calculating the dispatch date by counting backwards. =WORKDAY.INTL(DeadlineDate,-3,2) tells you: if this needs to arrive by X, we need to ship by Y, on a Tuesday-Saturday schedule. Operations managers tend to love people who think this way.

EOMONTH(start_date, months)

What it does: Returns the last day of the month that is a specified number of months before or after a date. Months = 0 gives you the last day of the current month.

Real-life example: You’re calculating invoice due dates set to the last day of the month following the billing date. =EOMONTH(A2,1) returns the last day of the month after any given billing date. January invoice? Due February 28 (or 29 in a leap year — Excel handles this correctly without you doing anything heroic). This alone is worth the price of knowing EOMONTH.

💡 Pro Tip: EOMONTH(A2,0)+1 gives you the first day of the next month. Because the day after the last day of a month is always the first day of the next one. Simple math, endlessly useful.

EDATE(start_date, months)

What it does: Returns the date that is exactly a specified number of months before or after a given date. Unlike adding 30, this respects the actual calendar.

Real-life example: Subscription renewals. Customer signs up on January 31. One month later is… February 28 (or 29). Not March 2. =EDATE(A2,1) handles this correctly where =A2+30 does not. If your subscription billing is off by a few days every month, there’s a good chance someone used A2+30 somewhere. Go find it.

💡 Pro Tip: Use negative values to go backwards in time. =EDATE(A2,-6) gives you the date six months before A2. Useful for calculating notice periods, lookback windows, and “when should we have started worrying about this.”

The “Convert Text to Actual Dates” Functions

DATEVALUE(date_text)

What it does: Converts a date stored as text into a proper Excel serial number (real date). Essential when importing data from systems that export dates as strings.

Real-life example: Your CRM export gives you dates like “April 16, 2026” stored as text. Excel won’t calculate with them. =DATEVALUE("April 16, 2026") converts it into a real date value you can actually use. Then format the cell as a date, and you’re back in business.

💡 Pro Tip: If your text dates are in a regional format Excel doesn’t recognize, DATEVALUE may give you an error. This is Excel’s polite way of saying “I have no idea what 16/04/2026 means in your locale.” In that case, combine MID, LEFT, RIGHT, and DATE to manually parse the components. Not glamorous, but effective.

TIMEVALUE(time_text)

What it does: Converts a time stored as text into an Excel decimal time value.

Real-life example: Your helpdesk system exports call times as text strings like “09:47:00 AM.” =TIMEVALUE("09:47:00 AM") returns 0.406944… which Excel recognizes as a time and can calculate with. Now you can compute average call duration, flag calls over a certain length, and build actual operational reporting instead of just staring at a column of text.

The “Format and Display” Functions

TEXT(value, format_text)

What it does: Converts any number or date into a text string with a specified format. Technically a general-purpose function, but utterly indispensable for dates.

Real-life example: You’re building an automated email subject line in a formula. You want it to say “Weekly Report – Week 16, April 2026.” ="Weekly Report – Week "&WEEKNUM(TODAY())&", "&TEXT(TODAY(),"MMMM YYYY") produces exactly that, updating itself every week. Your reports look like they were crafted by hand. They were not. That’s the whole point.

💡 Pro Tip: Common date format codes — “D” = day number, “DD” = zero-padded day, “DDD” = Mon/Tue/Wed, “DDDD” = Monday/Tuesday…, “MMM” = Jan/Feb/Mar, “MMMM” = January/February…, “YYYY” = four-digit year. Combine freely. Format to taste.

The “Rounding Times” Functions

FLOOR(number, significance) and CEILING(number, significance)

What they do: Round a time (or date) down (FLOOR) or up (CEILING) to the nearest specified interval.

Real-life example: You’re calculating billable hours and your company bills in 15-minute increments. A call lasts 47 minutes. Rounded up to the nearest quarter hour: =CEILING(A2,TIME(0,15,0)) gives you 1:00 — one hour billed. Rounded down: =FLOOR(A2,TIME(0,15,0)) gives you 0:45. Your billing system is now as pedantic as your accountant. That’s a feature.

MROUND(number, multiple)

What it does: Rounds a number to the nearest specified multiple. Works on times too.

Real-life example: You want to round all meeting times to the nearest 30-minute slot for a cleaner schedule view. =MROUND(A2,TIME(0,30,0)) rounds 10:17 to 10:00 and rounds 10:22 to 10:30. Schedule looks neat, everybody thinks you planned it this way. You absolutely did not plan it this way.

The Less-Famous Ones (That Are Secretly Brilliant)

YEARFRAC(start_date, end_date, [basis])

What it does: Returns the fraction of a year between two dates. The basis argument controls the day-count convention (actual/actual, 30/360, etc.).

Real-life example: You need to calculate pro-rated annual fees for a service contract that started mid-year. =YEARFRAC(ContractStart,ContractEnd,1) gives you something like 0.623 — meaning 62.3% of the year was covered. Multiply by the annual fee and you have the exact pro-rated amount. Finance teams use this constantly. Most people have never heard of it.

DATESTRING(date)

What it does: Converts a date to a text string in the system locale format. Note: this is a legacy function and availability varies by Excel version.

Real-life example: Mostly used in older workbooks or specific regional output requirements. You’re better off using =TEXT(A2,"DD/MM/YYYY") and controlling the format yourself. But if you encounter DATESTRING in someone else’s spreadsheet, now you know what it does instead of quietly panicking.

Putting It All Together: The Formula That Impresses Everyone

Want a formula that shows off everything? Here’s one for a project status dashboard.

Assume A2 is a project start date, B2 is a planned end date, and today is TODAY().

="Project is "&DATEDIF(A2,TODAY(),"D")&" days in. "&NETWORKDAYS(TODAY(),B2)&" working days remaining, due "&TEXT(B2,"DDDD, MMMM D")&IF(WEEKDAY(B2,2)>5," (⚠️ deadline falls on a weekend!)",".")

This one formula tells you how long the project has been running, how many working days are left, the deadline in plain English, and flags it if the deadline lands on a weekend. It updates every day automatically.

Is it a bit much for a Monday morning? Possibly. Does it make you look like an Excel wizard? Absolutely.

The Bottom Line

Excel’s date and time functions aren’t just a list of things to memorize. They’re a toolkit for turning raw timestamps into actual, actionable information — deadlines, durations, schedules, billing, planning, reporting.

Most people use three of them. Today you learned all of them. That gap between “most people” and you? That’s your edge.

Now go open a spreadsheet and do something useful with it. The deadline is closer than you think — and thanks to NETWORKDAYS, you now know exactly how much closer.

Here’s a cheatsheet for your troubles: https://blog.stibelman.com/excel-date-time-functions-cheatsheet/