+ Reply to Thread
Results 1 to 4 of 4

Need a formula to subtract 1 from another cell depending on the text input in another cell

  1. #1
    Registered User
    Join Date
    04-29-2021
    Location
    Birmingham, England
    MS-Off Ver
    Microsoft Office Professional Plus 2019
    Posts
    2

    Question Need a formula to subtract 1 from another cell depending on the text input in another cell

    Guys I need help, I am currently creating a timesheet for my worker however I have stumbled across a bit of a issue.

    Below is the timesheet to help understand the view and layout:

    Timesheet.jpg

    Column D: Time Start
    Column E: Time Finish
    Column F: Overtime
    Column G: Total Hours - Uses the Formula: =(E10+F10-D10)* 24
    Column I: Work Type Reason - Has a dropdown list of various reasons e.g, Worked without Lunch Break, Worked with Lunch Break, Holiday, Sickness

    I am trying to get the Total Hours to deduct 1 from the Column if the "Worked with a Lunch Break" is selected from the dropdown.

    Explanation for this is because the normal working hours total 8.5hrs however if they work 1 hour unpaid lunch 1 needs to be subtracted from the total working hours. I need this column to exist due to knowing the reasons in the dropdown however for this particular on I require a formula of such that detects this reason and automatically subtracts 1 from the Total hours Column.

    Please help....

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Need a formula to subtract 1 from another cell depending on the text input in another

    Change your formula to this:

    =(E10+F10-D10)*24 - (I10="Worked with a Lunch Break")

    then copy down.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    04-29-2021
    Location
    Birmingham, England
    MS-Off Ver
    Microsoft Office Professional Plus 2019
    Posts
    2

    Re: Need a formula to subtract 1 from another cell depending on the text input in another

    Quote Originally Posted by pete_uk View Post
    change your formula to this:

    =(e10+f10-d10)*24 - (i10="worked with a lunch break")

    then copy down.

    Hope this helps.

    Pete
    you are the man!!!!!! Nice work buddy thank you!

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Need a formula to subtract 1 from another cell depending on the text input in another

    You're welcome - glad to help.

    If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

    Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

    Pete

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 6
    Last Post: 12-31-2019, 05:27 PM
  2. Formula to change colour AND text in cell depending on value in adjacent cell
    By helsbels44 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-01-2017, 11:03 AM
  3. Cell calculation - Text query ( Add / Subtract depending on text )
    By Glorioso999 in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 09-14-2016, 10:33 AM
  4. Call from file depending on input cell. Formula help
    By pawilsonliftone in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 10-14-2014, 11:26 AM
  5. [SOLVED] Input text into a cell depending on the time
    By Robbosan in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-17-2013, 09:02 AM
  6. [SOLVED] IF formula variables to return more than one answer depending on another cell input
    By teenyjem in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 09-03-2012, 04:22 PM
  7. Value or formula in cell depending on input...
    By Jan Jansens in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-06-2005, 04:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1