Time Category Formular

[brandon_jrose]

Hi All,

Is is possible to have a formula/VBA that will provide time categories between 2 given DateTimes?

Eg.

StartDateTime: 1700, Sun Nov 20th 2016
FinishDateTime: 0700, Mon Nov 21st 2015

Time Categories:

Day Shift (0600-1800 Mon-Fri):1.0
Night Shift (1800-0600 Mon-Fri):6.0
Saturday (24hours):0
Sunday (24hours):7.0
Public Holiday:0
Total Hours:14

If hours fall within Public Holiday I don't need Day shift, Night shift, Saturday or Sunday hours that fall on the public holiday.

I've been trying myself with basic knowledge of formulas and if statements, but am struggling to find a solution.

Appreciate any help you could provide.

Thank you,
Brandon

[Aardigspook]

Welcome to the forum.

Yes, it's perfectly possible, though the exact formula gets more complicated depending on how many days your shift might span. If the shift will never be more than 24hours, then that's easier than if it might be more than 24hrs.
I'm just about to go out and won't be able to look at this until at least tomorrow (UK time) but I dealt with a similar problem not long ago. Have a look at sheet AS1 in the attachment in the post linked below and see if that's the sort of thing you'd want. If so, let me know and I can amend it for you (or someone else here might jump in first).
http://www.excelforum.com/showthread...=1#post4504992

[brandon_jrose]

That's exactly what I'm after!

I tried to make some changes myself, and found that I could change the values for Saturday so it would be the whole day, but it's not deducting from the ordinary times. Also don't know where to start with Public Holidays, would I need a separate table for public holiday dates?

Thanks for the help so far.

Cheers
Brandon