I don't know if this is possible. I can only hope.

In my attached time sheet, time is entered as 12-hour time and converted to 24-hour time. Then time is totaled (as an example) in column F. If the employee is off or on vacation, they enter OFF or VAC in the first cell of the day. Now I cannot add a formula to sum up the time because of these instances. Is there a way to sum the time IF time is entered. Then if any of these codes (BG, CHI, MTP, NB, OFF, T/0, VAC) are entered in the first cell of the day, I would like it to return 0 in the total column. Another issue I have is, the times may be from 11:00 PM on one day to 7:00 AM the next day.

This may be a long shot, but is this at all possible? PLEASE HELP!

Thank you in advance for your help.
Karen

Attached Files

Template.xlsx (29.6 KB, 4 views)

As about calculating totals, it is easy - use SUMIFS()! You add the time in row either when code cell is empty, or the code cell equals your single valid value for it, or OR(CodeCell=ValidValue1, CodeCell=ValidValue2, ...) = True, or OR(CodeCell<>"VASC", CodeCell<>"OFF",...) = True (using last 2 depending which has less values to compare).

About times. As it follows from your post, your time intervals can be over midnight. It is possible to calculate the length of time intervals for such cases using time value, but it means you always have to make an additional comparision to check, has the start time bigger value as end time (and this works only, when there never will be time intervals over 24 hours). Much easier (and much safer) way will be to enter start and end time values as datetime ones (like 28.11.2023 10:00). In case this is really a NO for you, the another option is to have calculated columns for StartDateTime and EndDataTime, and use them in your calculations.

About time formats you use - unless you enter times as strings, it doesn't count. The format used to display date or time don't change the value, it only changes how you will see it!

Something like the attached?
I changed the value of E9 to show an example of overnight hours.
This being said, your extensive use of merged cells will get you in trouble when you will want to analyze further data. Merged cells should be avoided at all costs.


Also, if I understand part of your problem, when summing a range, XL does not care about text

Attached Files

Template.xlsx (29.3 KB, 19 views)

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

@Pecoflyer

I very much appreciate your help. Great solution!

If it's not too much trouble, would you kindly explain the formula to me?
I know why you used ISNUMBER and the ranges, of course, but not sure of the rest.

Thank you again for your kindness.

Karen

Perhaps this will help Calculate number of hours between two times - Excel formula | Exceljet
Don't forget to mark the thread as " solves " ( see thread tools)

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

@ArviLaanemets

Thank you so much for all the great suggestions.

These functions are new to me and I will have to research them. I’m always up for learning anything new about Excel.

Thank you,
Karen