46 posts tagged with "Excel"

A cell's number format decides how its value looks, not what it is. The same value can show as 3.50, 12:00 or a date, depending on the format. In Excel for the web, the Number Format list on the Home tab changes it.

Payroll wants 3.5, not 3:30. Excel stores a time as a fraction of a day, so multiplying it by 24 gives hours: 3:30 × 24 = 3.5. Then set the result's format to Number, because Excel first shows it as a time.

A shift's paid time is the time between start and finish minus any unpaid break. With the break recorded in minutes, the formula is: MOD(Finish-Start,1) minus Break/1440. The MOD handles shifts past midnight and the /1440 turns minutes into a time.

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.