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

1. ## 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.

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

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

then copy down.

Hope this helps.

Pete

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

Originally Posted by pete_uk

=(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. ## Re: Need a formula to subtract 1 from another cell depending on the text input in another

You're welcome - glad to help.

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

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

#### 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