Forum Discussion

HunterJan10's avatar
HunterJan10
Copper Contributor
Jan 19, 2023

Formulas

Good morning, Everyone,

 

I am currently trying to figure out formula or function for my employee vacation tracker spreadsheet. I am not sure if it is possible, but what I would like to happen is when an employee uses a vacation day, I input "V" on the calendar and I would like for that input to take away 8 hours from their total vacation hours on another sheet. If I need to provide more explanation, please let me know. I appreciate any advice given.

Formulas and Functions

5 Replies

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Jan 19, 2023

    HunterJan10 

    =I2-SUMPRODUCT(($B$1:$E$1=H2)*($B$2:$E$28="V")*8)

    This formula could be a solution. The results can be displayed on another worksheet as well.

      

    • HunterJan10's avatar
      HunterJan10
      Copper Contributor
      Jan 19, 2023

      OliverScheurich How would I make the formula account for each monthly tab below? I have attached photos of my sheet. The employee's tab is where the total hours are located.

       

       

       

      • OliverScheurich's avatar
        OliverScheurich
        Gold Contributor
        Jan 19, 2023

        HunterJan10 

        =B2-SUMPRODUCT(($A2=january!$A$2:$A$23)*(january!$B$2:$AF$23="V"))*8-SUMPRODUCT(($A2=february!$A$2:$A$23)*(february!$B$2:$AC$23="V"))*8

        You can try a formula like this. For months march to december you can add a SUMPRODUCT expression to the formula accordingly. However there should be a solution with a shorter formula.