Data Validation Sum Calculation Formula

Question

I have created a sheet with data validation. It contains different work timings like 4-9 PM, 6-11 PM, Off, Leave and many more. the starting and ending time not in different cells but in one cell. so, I need to put a formula to get total hours and if off, should be not applicable output.t

Answer 1

Hi LG

I'm Anna and I'd be happy to help you with your question. In this Forum, we are Microsoft consumers just like yourself.

You can use a combination of the IF and SUMIFS formulas to achieve this.

First, you'll need to extract the start and end times from the cell containing the work timing. You can use the LEFT and RIGHT functions to do this, for example:

Start time: =LEFT(A1,FIND("-",A1)-1) End time: =RIGHT(A1,LEN(A1)-FIND("-",A1))

Then, you can use an IF formula to check if the work timing is "Off" and return 0 if it is:

=IF(A1="Off",0,(End time - Start time))

Finally, you can use the SUMIFS formula to sum the total hours, excluding "Off" values:

=SUMIFS(range, range, "<>Off")

You can use these formulas in the cells where you want to see the total hours calculated.

I hope this helps ;-), let me know if this is contrary to what you need, I would still be helpful to answer more of your questions.

Best Regards,

Anna

Give back to the community. Help the next person with this problem by indicating whether this answer solved your problem. Click Yes or No at the bottom.