Microsoft Office Tutorials and References

In Depth Information

=IF(OR(MOD(A1,400)=0,AND(MOD(A1,4)=0,MOD(A1,100)<>0)),TRUE,

FALSE)

Determining a date's quarter

For financial reports, you might find it useful to present information in terms of quarters. The following formula

returns an integer between 1 and 4 that corresponds to the calendar quarter for the date in cell A1:

=ROUNDUP(MONTH(A1)/3,0)

This formula divides the month number by 3 and then rounds up the result.

Converting a year to roman numerals

Fans of old movies will like this one. The following formula converts the year 1945 to Roman numerals:

MCMXLV:

=ROMAN(1945)

This function returns a text string, so you can't perform any calculations using the result.

Finally! The long-awaited companion function was introduced In Excel 2013. Use the

ARABIC function to convert a Roman numeral to a value. Here's an example, which re-

turns 1945:

=ARABIC(“MCMXLV”)

Time-Related Functions

Excel includes a number of functions that enable you to work with time values in your formulas. This section

contains examples that demonstrate the use of these functions.

Table 6-5 summarizes the time-related functions available in Excel. Like the date functions discussed earlier,

time-related functions can be found under the Date & Time drop-down list via Formulas
⇒
Function Library.

Table 6-5: Time-Related Functions

Function

Description

HOUR

Returns the hour of a time value

MINUTE

Returns the minute of a time value

NOW

Returns the current date and time