Excel Work Shift Calculation
October 16, 2018
Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.
Tomorrow, October 17th, is Spreadsheet Day -- an event that I started in 2010. It's growing bigger every year, and you can read more about it on my Spreadsheet Day blog.
October 17th was selected as the celebration day, because that was the date, in 1979, when VisiCalc was first released – that was the first spreadsheet for personal computers.
In some work places, people work on different shifts, such as 7 AM - 3 PM, 3 PM - 11 PM or 11 PM to 7 AM. If you're keeping track of maintenance issues, or other events, how can you calculate which work shift an event started in?
For example, if a machine broke down at 5 AM, what formula would put the shift number in cell B4?
My solution is below the screen shot. Did you do something similar, or use a different solution?
For this type of problem, I'd make a lookup table with the Shift information. Shift start times are in the first column, and shift numbers are in the second column.
Each day starts at 12 midnight, so I put that time at the top of the list, just like you'd start with a zero in other lookup tables. Then, in cell B4, I entered this formula, to calculate the shift number:
Note: TRUE returns an approximate match, and that is the default, so you could omit it.
Find more examples for the VLOOKUP Function on my website
Here are a few Excel articles that you might find useful or interesting.
Passwords - Doug Glancy shows how to make a Password form in Excel, with a Cap Lock warning, and a "Show Text" button. This could be useful in workbooks where you allow some users to unhide sheets, or do other special tasks. (Level - Int/Adv)
Shortcuts - Harvard Business Review has a list of 10 Excel features and shortcuts that everyone should know. Does it really take 15 minutes to learn Freeze Panes? (Level - All)
Reminder - Jon Acampora is running free webinars on macros & VBA this week. Choose any date from today until Friday, October 19th. Jon explains why you'd want to learn VBA and shows how to get started. Then, if you'e like to learn more, check out Jon's popular VBA Pro Course. Sign up soon - registration is only open until Oct. 25, 2018.
We celebrated Canadian Thanksgiving last week, and then we had a few days with summer weather, and now it's cool again. But at least we're not in Calgary, where they had about a foot of snow last week!
The tree in front of our house is showing its fall colours, with dazzling gold leaves. The colours almost look fake in this photo, but that's what the sky and leaves looked like! I think it's a mountain-ash, but I've been wrong before.
That's it for this week! If you have any comments or questions, send me an email.
NOTE: For the online version, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2018/20181016ctx.html
I'll also post any article updates or corrections there.
Last updated: October 12, 2018 3:52 PM