79 posts tagged with "Microsoft"

AND checks several conditions at once and returns TRUE only if every one of them is true. =AND(A10<$C$7,B10>$C$7) asks "did this shift start before C7 and finish after it?". And because Excel treats TRUE as 1 in arithmetic, the answer can be added straight into a total.

TIME(hours, minutes, seconds) makes a time from numbers, and adding it to a date gives a date and time: =B6+TIME(1,0,0) is 01:00 on the date in B6. Give the result a format that shows times, or you'll only see the date.

UK clocks change on the last Sunday of March and of October, and plenty of other rules use "the last Sunday of the month" too. Excel works it out from two numbers: the first day of the next month, and the weekday number of the day before it. First of next month − WEEKDAY(day before) is the last Sunday.

WEEKDAY tells you which day of the week a date falls on, as a number: by default 1 for Sunday up to 7 for Saturday. It's the building block for "the last Sunday of the month", "the next Monday", or anything else that depends on the day of the week.

When the year is in one cell and you need a date in it, don't type the date: build it. =DATE(year, month, day) makes a real Excel date from three numbers. =DATE(B1,4,1) is 1 April of whatever year B1 holds, so changing the year moves the date with it.

A total of times needs a different format from a time of day. hh:mm shows the clock, so 60 hours 15 minutes shows as 12:15. The custom format [h]:mm shows elapsed hours: 60:15.

SUM adds up numbers. Give it a range: =SUM(B2:C2) adds a row, =SUM(B2:B4) adds a column, and =SUM(OvertimeLog[Time worked]) adds a whole table column. It's the first function most people learn, and every total in a timesheet uses it.

To add up only the rows dated within a range, give SUMIFS two conditions on the same date column: on or after the start, ">="&start, and before the end, "<"&end. The comparison sign goes in quotes and & joins it to the cell with the date.

A log of shifts answers "who worked when"; payroll asks "how many hours did each person do each month?" One SUMIFS formula, copied across a grid of names and months, answers it: it adds up Hours where the Employee matches the row and the Date falls in the column's month.

When you copy a formula, Excel moves its references with it: A2 copied one row down becomes A3. A $ stops that. $C$7 never moves, $A2 keeps its column but moves its row, and B$1 keeps its row but moves its column. Mixed references let one formula fill a whole grid.