Finance

Calculate first day in month relative to a date

Type:   =EOMONTH(A1,2)+1

Where A1 represents the date, 2 represents the number of the month for the date.

Calculate last day in month relative to a date

Type:   =EOMONTH(A1,6)

Where A1 represents the date, 6 represents the number of the month for the date.

Calculate calendar quarter from a date - Excel

Type   =INT((MONTH(A1)-1)/3)+1   

Where cell A1 contains the date

Calculate financial quarter from a date - Excel

Type   =MOD(CEILING(22+MONTH(A1)-6-1,3)/3,4)+1   

Where cell A1 contains the date, 6 (month 6) represents last month of the financial year.

Format cell as number with no decimal places.

Insert current time - Excel

Type   =NOW()   Volatile formula updates each time refresh data.

Insert current date - Excel

Type   =TODAY()   Volatile formula updates each time refresh data.

Filename & path displayed within a cell - Excel

Type   =CELL("filename")

Comment within a formula - Excel

Type   +N("comments")   at the end of the formula