Calculating the number of past days

Employees working in HR often make budgets influenced by the number of past days at some time. Excel has a function that calculates how many worked days passed between two dates, as well as a function that calculates the date that is obtained if we add a certain number of working days on the given date. These functions may also be useful to salespeople when they set payment deadlines or liquidators when they determine when they should settle their debit obligations.

Date and time functions

When the reports contain dates and/or times, it is useful to know how to use the date and time functions. These functions allow us first to retrieve the system date and/or time and to display it within the cell. Then, by using them, we can distinguish a date/time unit (day, month, year, hour, minute, second, etc.) and use them to calculate the time or date in the future. For example, a date that comes in 5 days …

Searching through data ranges

In practice, it often happens that we have tables that are in a mutual relationship. Similar to the database, the value of a table column column may be a key by which we can access the columns of another table. In this text, we will discuss the functions used to search through data ranges. If you master them, it’s quite certain that this will quickly become one of your favorite Excel tools.

Array formulas

Excel supports a special type of formulas that allows us to use arrays of variables as arguments rather than variables (references, ranges, names), where we can perform certain mathematical operations over all of them. Moreover, there is a situation where the arrays considerably shorten the way to the solution or are even necessary to get to it. In the following text, through a few examples, it will be shown how to use array formulas.

Calculating the amount

When making business reports it is often necessary to calculate the sum of the amount as a product of quantity and price which are stored in the columns of the table. For this purpose, we can manually create formulas, and we can also use SUMPRODUCT function, which has a goal to summarize products within the range for given arguments. Let’s say something more about this, not so known, function …