Help with Cell Subtracting if Certain List item is selected

[redneck9407]

I am building a time sheet for work. I am needing to get cells E10:D10 to subtract into cell F10 if the list item 6: Travel Time has been selected.

For Example:
Cell A10= 6: Travel Time
Cell F10= Recognizes that travel time has been selected and subtracts cell E10:D10 and enters the total amount.

Another Example is:
Cell A11= 1: Office Time
Cell G11= Recognizes that Office Time has been selected and subtracts cell E10:D10 and enters the total amount.

I have already built the control page and the list into the worksheet. I am not that good with all the formulas and macros associated with Excel yet. I have been working with it a lot as of late and part of the degree I am getting has associated me with Excel on a regular basis.

Any help is greatly appreciated.

Thanks
John

[Melvinrobb]

Please Login or Register  to view this content.

Replace XXXX with whatever formula you want used when A10=travel time.

If this isn't what you are looking for, uploading a sample workbook would help with clarrification.

[Michael Kuchman]

Will cells D10 & E10 always have numeric values?

[redneck9407]

They will always be time format.

Thank you for the formula. Since there 13 other selections that could go into the On-Site time I can separate with a comma correct and input there names?

[redneck9407]

*** Time sheet Bi-weekly.xlsx

Please excuse the name of the file. It is the abbreviation of the company. The formula works but after the "Travel Time" is selected it spits out a odd number instead of a whole number or with one decimal place.

[Melvinrobb]

Are you wanting this formula in F10 (and drag down):

Please Login or Register  to view this content.

[Melvinrobb]

I believe the issue you are having is with the formatting. just go to cell H10, and apply a "Time" format.

[redneck9407]

That is formula I currently have in place. However, it is giving me a .04 number instead of 1 which it should.

Edited-----
I reformatted to the time format as suggested. All is working as of now with that formula. Thank for the help.

Like the below I posted above to include the rest would I just seperate with a comma? Also, when I tried to do this it told me I needed to include the concatenate function. How would I include that into that formula?

Sorry for all the questions and thanks for ALL the help!

[redneck9407]

Also, for the other selections it will need to go to the On-site time. For that I will simply do.

=IF(A10="1: Office Time, 2: Service (Contract), 3: Service (Chargable), 4: Installation (Contract), 5: Warranty Service, 7: Lunch, 8: Paid Holliday, 9: Paid Vacation Time, 10: Unpaid Time Off, 11: Sales Call, 12: Oncall - Chargeable Service, 13: Oncall w/ Service Agreement", E10-D10,"")

Correct?

Now to throw another hitch in there. I need for when "Lunch" is selected for it to automatically make this a negative number so that it is subtracted out of the total time for the day. (My boss is rather lazy)

[Melvinrobb]

I would go with something similiar to the attached file. If you are going to have negative values, I would not have them formatted as time.
the way time values work in excel, 1 is considered to be a day. so .04 is the equivalent of 1 hour. if we take E10-D10 and multiply it by 24 , we turn .04 Days into 1.00 Hrs.
You can then have negative values showing in columns G and H.

[redneck9407]

This is Very much like what I am looking for. However, why does it show that there is only a total of 1 hour instead of 2 at the total?

[Melvinrobb]

Because the Lunch hour is a negative value. If you want lunch to be ignored, put the following formula in G10 and drag down:

Please Login or Register  to view this content.

By ignoring lunch, you will always have positive values, so you could erase the "*24" from the formulas, and format the cells as "Time" if desired.

[redneck9407]

There is a total of 3 things listed on the form you sent me.
1st is the travel time equaling 1 hr
2nd is the Lunch equaling -1hr
3rd Sales Call equaling 1hr.
But at the bottom at the total it still shows only 1 hour worked. Shouldnt it show two?

[Melvinrobb]

1 HR - 1 HR + 1 HR = 1 HR
If you want it to show 2, you will need to ignore the lunch times, and return "0.0", not "-1.0"

[redneck9407]

OOOOO.. and now I feel dumb. That made it click. I changed the format to the 0 which is PERFECT!

Thank you for ALL your help!!!!!