Contextures News 20181016

Excel Work Shift Calculation

October 16, 2018

Calculate the shift number, Spreadsheet Day, and more, in this week's Excel news. Visit my Excel website for more tips, tutorials and videos, and check the index for past issues of this newsletter.

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.

Which Work Shift?

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:

=VLOOKUP(A4,\$D\$4:\$E\$7,2,TRUE)

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

Excel Articles

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.

Also see: My Excel Products || Excel Events || Previous Issues || Weekly Humour

Fall Colours

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.

I'll also post any article updates or corrections there.

Debra Dalgleish
dsdalg@ gmail.com

P.S. You can choose the full Contextures news package (news and occasional special announcements), or basic news package (news only). Click either link to change your option.

Last updated: October 12, 2018 3:52 PM