How to Use the NOW Function to Calculate Time in Excel

The NOW function in Excel returns the current date and time, updated continuously when a worksheet is changed or opened. The NOW function is useful when you need to display the current date and time on your spreadsheet, or when you need to calculate durations or deadlines based on the current date and time.

The syntax of the NOW function is:

=NOW()

The function has no arguments and returns a serial number that represents the date and time. To format the result as a date and time, you can use the number format options in Excel, or use the TEXT function to specify a custom format.

Example

Suppose you want to calculate how much time has passed since a certain date and time, such as the start of a project or an event. You can use the NOW function to get the current date and time, and then subtract the start date and time from it. For example, if the start date and time is in cell A2, you can use this formula in cell B2:

=NOW()-A2

This will return the elapsed time in days, hours, minutes, and seconds. To format the result as a duration, you can use the custom number format:

[d]:hh:mm:ss

This will display the number of days, followed by a colon, and then the hours, minutes, and seconds.

Here is an example of a table that shows the start date and time of some tasks, and the elapsed time since then, using the NOW function and the custom number format:

The result of the NOW function will change every time the worksheet is recalculated or opened, so the elapsed time will be updated accordingly.

Alternative Methods

There are other ways to calculate time in Excel, depending on your needs. Here are some alternative methods:

• If you want to get the current date only, without the time, you can use the TODAY function, which has the same syntax as the NOW function, but returns only the date as a serial number.
• If you want to get the current time only, without the date, you can use the TIME function, which takes three arguments: hour, minute, and second. For example, to get the current time as of now, you can use this formula:

=TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()))

This will return the current time as a decimal number between 0 and 1, representing the fraction of the day. To format the result as a time, you can use the number format options in Excel, or use the TEXT function to specify a custom format.

• If you want to calculate the difference between two dates and times, you can use the DATEDIF function, which takes three arguments: start date, end date, and unit. The unit can be one of the following: Y (years), M (months), D (days), MD (days ignoring months and years), YM (months ignoring years and days), or YD (days ignoring years). For example, to calculate the number of days between two dates and times in cells A2 and B2, you can use this formula:

=DATEDIF(A2,B2,"D")

This will return the number of days between the two dates and times, ignoring the time part. To include the time part, you can use the “+” operator to add the time difference to the date difference. For example, to calculate the exact duration between two dates and times in cells A2 and B2, you can use this formula:

=DATEDIF(A2,B2,"D")+(B2-A2)

This will return the duration in days, hours, minutes, and seconds. To format the result as a duration, you can use the custom number format:

[d]:hh:mm:ss