Microsoft Technologies and Services
View All Tags
Excel measures time in days, so a number of minutes isn't a time until you convert it. A day has 24 × 60 = 1440 minutes, so minutes / 1440 is the time: 30 / 1440 is half an hour. That's how a timesheet's Break (mins) column becomes something you can subtract from hours worked.
A shift from 22:00 to 06:00 is eight hours, but =Finish-Start in Excel gives a negative time and shows ########. =MOD(Finish-Start,1) gives 08:00: it counts forward from the start time to the finish time, past midnight if needed, and leaves daytime shifts as they were.
Excel stores times as numbers, so the time between two of them is a subtraction: finish minus start. A shift from 19:00 to 23:00 gives 04:00. This is where every timesheet's hours begin, and it needs one more step for shifts that end after midnight.
In an Excel table, a formula belongs to the whole column. Type a heading next to the table and Excel adds the column; type a formula in its first row and Excel fills every row. That's a calculated column, and when you change its formula, the whole column changes with it.
Inside an Excel table, formulas can name columns instead of cells. =[@Finish]-[@Start] reads as "this row's Finish minus this row's Start", which is clearer than =E2-D2 and keeps working when rows are added or moved. These are structured references.
Write a formula once, then copy it to the other rows. Excel adjusts the cell references as it pastes, so row 11's copy uses row 11's cells, while references with $ signs stay pointing at the same cell. Select, Ctrl+C, select the target cells, Ctrl+V (Cmd+C and Cmd+V on a Mac).
The formula bar is the long box above the grid, next to fx. It shows what the selected cell really holds, a formula or the full value, and you can type into it. For a long formula, it's easier than typing in the cell.
An Excel formula is a calculation in a cell. It always starts with =, it can use other cells by their addresses, like B10, and when you press Enter the cell shows the result while the formula stays behind it. This guide enters one that turns a shift's start and finish into hours.
Excel names new tables Table1, Table2 and so on. A real name such as OvertimeLog makes formulas readable, OvertimeLog[Hours], and lets other tools find the table. Rename it in the Table Name box on the Table Design tab.
An Excel table is more than colours. It gives your rows a name, a filter button on every heading, and columns whose formulas fill down by themselves, and it grows when you add a row. Home → Format As Table turns a plain range into one.
