Okay, so I am creating an electronic timesheet (see below). The issue I am having is the first "In/Out" rows are
deemed "1st Shift", the second "In/Out" rows are "2nd Shift", and the 3rd "In/Out"
rows are "3rd Shift". When these are hand written, third shift (11pm to 7am) puts in 2300 - 2400 in the "3rd
Shift" row on the day it is relevant to, and then puts 0000 - 0700 in the "1st Shift" row the following day.
When a person looks at this handwritten timesheet, it is understood that 0000 - 0700 is 3rd shift from the night before, even
though it is put in the 1st shift row. Clear as mud?
So, wouldn't it be nice if Excel could simply subtract 0700
on day 2 from 2300 the night before and have it = 8 hours. Unfortunately (as far as I can tell) it is not possible. So, I
thought of changing the times (as seen in column W) to text, and just making them labels that equal the values located in
column Y. This way I could have someone work 11pm to 11am (2300 - 1100) and be able to put in the 3rd shift row "2300
for "In" and 1100 for "Out" (all in the 3rd Row In/Out on the night where the shift started) and have it
understand that 2300 = 23.00 and 1100 = 35.00; therefore 35 - 23 = 12 Hours for 3rd Shift???
I hope my description
and screenshot are sufficient to get my question answered; please let me know if any futher clarification is needed. Thank
you in advance for any assistance anyone can provide! The link below will take you to a screenshot of what I describe.
P.S. I thought about using If/then statements (for example IF D19 = W10, then Y10), but I am limited to like 30 or
60 of these, and I would need 144 of them to cover values for everything between 0000 on day 1 and 1100 on day 2 based on
every quarter hour (total of 12 hours to include the .25, .50, and .75 for 15 past the hour, 30 past the hour and 45 past the
hour...36 Hours x 4 = 144)