Convert decimal hours to Excel time
In the Excel date system, one day is equal to 1, so you can think of time as fractional values of 1, as shown in the table below:
| Hours | Fraction | Value | Time |
|---|---|---|---|
| 1 | 1/24 |
Dave Bruns
Year is a leap year
In this example, the goal is to write a formula that will return TRUE if a year is a leap year and FALSE if not. This is a surprisingly challenging problem in Excel for two reasons: (1) Excel thinks 1900 is a leap year due to a long-standing bug inherited from Lotus 1-2-3 and (2) The logic for...Read more
Extract date from a date and time
Excel handles dates and time using a scheme in which dates are serial numbers and times are fractional values. For example, June 1, 2000 12:00 PM is represented in Excel as the number 36678.5, where 36678 is the date portion...Read more
Get last day of month
In this example, the goal is to get the last day of the month based on any valid date. This problem can be solved most easily with the EOMONTH function. However, it can also be solved with the DATE function as explained below.
The EOMONTH function...Read more
Get workdays between dates
The Excel NETWORKDAYS function calculates the number of working days between two dates. NETWORKDAYS automatically excludes weekends (Saturday and Sunday) and can optionally exclude a list of holidays supplied as dates.
For example, in...Read more
Get quarter from date
In this example, the goal is to return a number that represents quarter (i.e. 1,2,3,4) for any given date. In other words, we want to return the quarter that the date resides in.
ROUNDUP formula solution
In the example shown, the formula in cell C5 is:...Read more
Get nth day of year
This formula takes advantage of the fact that dates are just sequential numbers in Excel. It determines the last day of the previous year and subtracts that value from the original date with this formula:
=B5-DATE(YEAR(B5),...Read moreCalculate years between dates
In this example, the goal is to calculate the number of years between a start date in column B and an end date in column C. An easy way to solve this problem is to use the YEARFRAC function, which returns the number of years between any two dates as a decimal number.
YEARFRAC function...Read more
Convert text to date
The DATE function creates a valid date using three arguments: year, month, and day:
=DATE(year,month,day)
In cell C6, we use the LEFT, MID, and RIGHT functions to extract each of these components from...Read more