Navigating through dates and times in Microsoft Excel can often seem like a dance with numbers. As someone who spends a hefty amount of time juggling spreadsheets, I can tell you that mastering date and time functions is pretty much a game-changer. From tracking important deadlines with the WORKDAY function to scheduling reports with the powerful TODAY function, there’s a whole toolbox available to make date and time manipulations a breeze.

Whether you’re a newcomer or a seasoned Excel aficionado, understanding how to effectively use functions like WEEKNUM to retrieve the week number or DATEDIF to calculate the difference between two dates is crucial. The good news is that Excel has evolved over the years, and with versions stretching from Excel 2013 to the latest Microsoft 365 and Excel 2021, these functions have only become more refined and user-friendly. Handling date and time in Excel is about knowing the right function for the task at hand, whether it’s calculating elapsed time or converting dates into a readable number format.

1. HOUR Function

Syntax

In using the HOUR function, I stick to a simple syntax:

HOUR(serial_number)

Arguments

The argument I use:

  • serial_number: This is the specific time I’m interested in extracting the hour from. It can be a cell reference or a time value formatted as text.

Example

I often find myself using the HOUR function in different scenarios:

  1. Use Cell Reference
    When I have the time entered in a cell, for instance, if it’s 3:45 PM in cell A1, I use =HOUR(A1) and it cleverly gives me 15, reflecting the 24-hour clock.

    excel-hour-function-example-1

  2. Use Date as a Reference
    Sometimes when I’m given a date with a time, the HOUR function helps me isolate the hour part. For example, with a combined date and time in cell B1, =HOUR(B1) will extract the hour from that value too.

    excel-hour-function-example-2

    Plus, if I switch the cell’s format to a number, the time portion appears as a decimal, confirming the time is encoded there.

    excel-hour-function-example-3

2. MINUTE Function

Syntax

MINUTE(serial_number)

Arguments

  • serial_number: The time input from which I want to extract the minute portion. It should be a number representing the time of the day.

Example

I can extract minute values from various times like so:

excel-minute-function-example-1

3. NOW Function

Syntax

The way to use it is simple; just type =NOW() in a cell where you want the date and time to appear.

Arguments

I don’t need to include any arguments with the NOW function. It’s important to remember that it updates its value automatically every time something in the worksheet changes.

Example

I’ve played around with the NOW function a bit, and here’s what I found:

  • Using the NOW function to get the current time, I combined it with the TEXT Function: =TEXT(NOW(),"h:mm AM/PM") to display time in a readable format.
  • When I set the cell format to “General”, I noticed that dates and times are expressed as serial numbers in Excel. The number before the decimal is the date, and after the decimal is the time.

4. SECOND Function

Syntax

Here’s how simple it is to write it down:

SECOND(serial_number)

Arguments

  • serial_number: Just toss in any time. What I’ll do is pluck out the seconds part and leave the rest behind.

Example

So, you feed me a time, like in these neat little pictures:

excel-second-function-example-1

If there aren’t any seconds to show, guess what you’ll get? A big, round 0!

excel-second-function-example-2

5. TIME Function

Syntax

Here’s the simple formula I use:

TIME(hour, minute, second)

Arguments

When I talk about the TIME function’s components, here’s what I mean:

  • hour: This is where I put the hour value.
  • minute: I drop in the number representing the minute here.
  • second: And here’s where the second value goes.

Example

Say I want to create a specific time value. I just plug in the numbers for hours, minutes, and seconds like this:

  • Inputting zero in the hour field fetches me the time at midnight.
  • If I put in a negative number, it subtracts from the total time—taking me back to the previous hour, minute, or second.
  • After I enter all the numbers, I get a decimal number that stands for the time in Excel. If I want to sum up or subtract time values, it’s a breeze with this function.

And yeah, the times I get from this function stick to a 12-hour clock.

6. TIMEVALUE Function

Syntax

The formula looks like this:

TIMEVALUE(time_text)

Arguments

Example

Let’s dive right into an example that illustrates how this function is used in action:

Example of TIMEVALUE function

Suppose I have “2:45 PM” as text. When I input it into the function like this:

=TIMEVALUE("2:45 PM")

I get back a decimal. This decimal corresponds to the percentage of a 24-hour day that has passed. I can then format this decimal into a more recognizable time format.

If I enter a time with no AM or PM, don’t worry – the function assumes I’m using a 24-hour clock. It’s neat that it can strip the time from a date-time combination as well. Just make sure that whatever I feed into TIMEVALUE is something Excel recognizes as a valid time or date-time. It makes life easier when manipulating and comparing times since Excel digests numbers much more efficiently than text.

Similar Posts