Purpose
Return value
Syntax
=NOW()
Using the NOW function
NOW takes no parameters but requires empty parentheses. The value returned by NOW will continually update each time the worksheet is updated, for example, each time a value is entered or changed. Use F9 to force the worksheet to recalculate and update the value.
The value returned by the NOW function is a standard Excel date , including a fractional value for time . To display the result as a date, apply a date number format . Optionally, customize the number format to include the time. If you want the current date without a time value, use the TODAY function .
The NOW function explained
The NOW function returns the current date and time according to the settings on your computer. As I write this, the date is June 22, 2024, and it’s just after 10 AM in the morning. When I enter the formula below in cell A1, it returns the decimal number shown below:
=NOW() // returns 45465.42441
To see the number returned by NOW, apply the General number format (Control + ~).
The integer part of the number (45465) is the date (June 22, 2024), and the decimal part of the number (0.42441) is the time (about 10:11 AM). Note that you will need to apply a number format to display the date and/or time as required. After working for a few minutes in Excel, NOW returns a new result:
=NOW() // returns 45465.42721
Notice the number has increased slightly. The date (45465) is the same (June 22, 2024), but the time (0.42721) is now about 10:15 AM. The NOW function will continue to update this value each time the worksheet is changed, saved, or opened. To display the number returned by NOW as a date and/or time, you will need to apply a suitable number format . Here are three examples:
"d-mmm-yyyy" // a date like "22-Jun-2024"
"h:mm" // a time like "10:15"
"m/d/yyyy h:mm" // a date and time like "6/22/2024 10:15"
Note: the NOW function returns the current date and time. The similar TODAY function returns just the current date.
Example - basic usage
The examples below show how the NOW function can be used in various ways:
=NOW() // current date and time
=NOW()-7 // last week same time
=NOW()+7 // next week same time
=NOW()+90 // 90 days from now
=MROUND(NOW()+90,"1:00") // 90 days from now to nearest hour
=EDATE(NOW(),3) // 3 months from now, time removed
=EDATE(NOW(),12) // 12 months from now, time removed
=EOMONTH(NOW(),-1)+1 // first day of current month
=EDATE(NOW(),6)+MOD(NOW(),6) // 6 months from now, time preserved
Current time without a date
The NOW function returns the current date and time. However, in some cases, you may want to return only the current time. You can do this by adding the MOD function as shown below:
=MOD(NOW(),1)
The MOD function returns the remainder after division. The first argument is the number, and the second is the divisor. By using a divisor of 1, the remainder will be the decimal part of the number since every whole number can be evenly divided by itself.
Hours and minutes before a specific time
Why would you want to remove the date? One example is the problem of displaying the hours and minutes that remain before a specific time of the day. For example, the formula below will display the time that remains before 6:00 PM:
="18:00"-MOD(NOW(),1)
Without removing the date, the value from NOW would always be larger than the time “18:00” which would create a negative number. Excel does not support negative time values and will instead display a string of hash characters like “######”. Of course, the result from the formula above will turn negative when the current time has passed 6:00 PM. To force negative values to zero, you can add the MAX function like this:
=MAX("18:00"-MOD(NOW(),1),0)
Here, the MAX function is a clever way to avoid an IF statement. If the time remaining is positive, MAX will return that value. If the time that remains is negative, MAX will return zero. To see hours and minutes, format the result with the number format like “h:mm”.
Static date and time
If you need a static date and time that won’t change, you can use the following shortcuts:
- Insert current date - Control + ;
- Insert current time - Control + Shift + :
To enter both values in a single cell, enter the date, a space, and then the time.
Formatting results
The result of NOW is a serial number representing an Excel date and time . You can format the value returned by TODAY using any standard date format . You can use the TEXT function to build a text message that includes the current date:
="The date is "&TEXT(NOW(),"mmm d")&" and the time is "&TEXT(NOW(),"h:mm AM/PM")
To return a text string like “The date is May 31 and the time is 6:10 PM”.
Purpose
Return value
Syntax
=SECOND(serial_number)
- serial_number - A valid time in a format Excel recognizes.
Using the SECOND function
The SECOND function extracts the second component from a time as a number between 0-59. For example, given a time of “12:15:01”, SECOND will return 1. The SECOND function takes just one argument , serial_number , which must be a valid Excel date , a valid Excel time , or a text value Excel can interpret as a time (e.g. “7:45:30 PM”).
The SECOND function does not convert time into seconds but rather extracts the seconds component from time. For example, given a time duration of 10 minutes (600 seconds) the SECONDS function will return 0 (zero), since seconds are zero in the time 10:00. This means the SECOND function will “reset” to 0 every 60 seconds (like a clock). To convert a time value into decimal seconds, see this example . To create a time value from scratch with separate hour, minute, and second inputs, use the TIME function .
Examples
When given the time “10:45:17 AM”, the SECOND function will return 17:
=SECOND("10:45:17 AM") // returns 17
With the time “2:19:36” in cell A1, SECOND will return 36:
=SECOND(A1) // returns 36
The formula below demonstrates how the TIME function can be used to create the time 9:30:45 in Excel:
=TIME(9,30,45)
If we wrap the SECONDS function around the TIME function, we get 45, as expected:
=SECOND(TIME(9,30,45)) // returns 45
Note: Excel stores dates as serial numbers and times as decimal numbers . For example, the time 12:00 PM is equal to 0.5 (one half-day), and the date Jan 1, 2000 12:00 PM is equal to the serial number 32526.5 in Excel.
Fractional seconds
One notable limitation of the SECOND function is that it rounds fractional seconds to the nearest second. You can see this behavior in the worksheet below. The SECOND function is configured to extract seconds from the times in column C, which are recorded in hundredths of a second. During this operation, the fractional part of the number is lost when it is rounded:
To extract fractional seconds while maintaining precision, you can use a formula like this:
=MOD(C5*1440,1)*60
For a detailed explanation of this formula, see Time in hundredths of a second .
