The first part of the solution uses the MIN and TODAY functions to find the "next date" based on the date today. This is done by filtering the dates through the IF function:
IF((date>=TODAY()),date)
The logical test generates an array of...Read more
The WORKDAY function returns a date in the future or past, based on a start date, workdays, and optional holidays. WORKDAY automatically excludes weekends, and counts only Monday through Friday as workdays.
In the example shown, WORKDAY is configured to get a project midpoint date by...Read more
This formula uses the WORKDAY function to calculate an end date. WORKDAY can calculate dates in the future or past, and automatically excludes weekends and holidays (if provided).
In the example shown, we have the project start date in column C, and days in column D. Days represents the...Read more
Note: in the current version of Excel, the FILTER function is a better way to solve this problem. The INDEX and MATCH formula explained here is meant for legacy versions of Excel that do not provide the FILTER...Read more
In the Excel time system, one 24-hour day is equal to 1. This means times and hours are fractional values of 1, as shown in the table below:
| Hours | Time | Fraction | Value |
|---|---|---|---|
| 1 |
In the Excel time system, one 24-hour day is equal to 1. This means times and hours are fractional values of 1, as shown in the table below:
| Hours | Time | Fraction | Value |
|---|---|---|---|
| 1 |
In the Excel time system, one 24-hour day is equal to 1. This means times and hours are fractional values of 1, as shown in the table below:
| Hours | Time | Fraction | Value |
|---|---|---|---|
| 1 |
This formula uses the SUMPRODUCT function to sum the result of two expressions that yield arrays. The goal is to sum only time greater than 30 minutes, the "surplus" or "extra" time. The first expression subtracts 30 minutes from every time in the named range "times":
...Read more
Note: This example assumes the start date will be provided as the first of the month. See below for a formula that will dynamically return the first day of the current month.
With the layout of the grid as shown, the main problem is to calculate the date in the first cell in the...Read more
Dates in Excel are just serial numbers, formatted to display as dates. This means you can perform math operations on dates to calculate days in the future or past.
In the example shown, the date in the named range "start" is provided by the TODAY...Read more