Microsoft Excel and Excel for the Web Formulas, Tables, Formatting and Dates
View All Tags
Column settings can check one column at a time. List validation checks a whole item when it's saved, so it can compare columns: a finish after its start, an end date after a begin date, a discount below the price. If the formula isn't TRUE, SharePoint refuses the item and shows your message.
Many teams still record overtime in a shared Excel workbook: who worked, when, why, and who said yes. This series replaces it with a Power Apps app on SharePoint. Staff log overtime in a few taps, the hours are calculated and totalled for them, each department's approvers approve it in Teams or Outlook, and payroll gets a monthly Excel workbook and a Power BI report. Everything runs on Microsoft 365.
On the night the clocks go back, a 22:00 to 06:00 shift lasts 9 hours. Excel says 8, because its dates and times know nothing about clock changes. In spring it's the other way: the shift lasts 7 hours and Excel still says 8. This guide works out when the UK clocks change in a given year and corrects each shift's hours.
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.
