Excel-Tips

Basic overtime calculation formula!!
We can use a set of formulas to calculate the pay associated with workers working overtime. The steps below will guide all levels of excel users through the process.

The INDEX MATCH[1] Formula is the combination of two functions in Excel: INDEX[2] and MATCH[3]. =INDEX() returns the value of a cell in a table based on the column and row number. =MATCH() returns the position of a cell in a row or column.

Viewing the Formula in Another Cell in Excel

FormulaText function will show the formula, while the original value stays put, making it easier to understand the formula and its working, while having the value displayed directly.

=FORMULATEXT(cell_reference to a cell containing a formula)

The Excel function EOMONTH() returns the date of last day in a month. With a little extra, it’ll tell you the number of days in any month.

=EOMONTH(start_date, months)

⏺️ start_date : this is the initial date. Make sure to enter your date using the DATE function to avoid any errors which may incur from entering the date as text.

⏺️months : the number of months before or after start_date. A positive value for months produces a future date; a negative value creates a past date; Zero for the current month.

How to convert date to text month?

LOWER, UPPER and PROPER are text functions which let us do the following:

LOWER – Converts all words in the text to lowercase.

UPPER – Converts all words in the text to uppercase.

PROPER – Capitalizes the first letter in each word in the text.

Syntax

=LOWER(text)

=UPPER(text)

=PROPER(text)

How to use the SUMIF function in Excel to add the values in a range that meet criteria that you specify.

Use the COUNTBLANK function, one of the Statistical functions, to count the number of empty cells in a range of cells.

It is typed =COUNTIFS:

=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)

The COUNTIFS function is a premade function in Excel, which counts cells in a range based on one or more true or false condition.