Created drop box (shift description) - unable to code cell to report hours

Merry Christmas/Happy Holidays to all. I'm a new comer to this site - I'm really hoping someone can help me out.

I have created a shift_timesheet.xls and am hitting a solid 4ft deep brick wall! I'm unable to formulate a code to accept my drop down box (ie. Cell D6), to automatically report (hours) on another cell (D7). My drop down box has roughly 10 different shifts we use here at work, and I would like to select the shift (D6), and it report that shifts hours in cell D7.

I hope this makes sense. I've attached an image so you can see where im coming from.

mdurkin

Hi

Can you post a sample worksheet so we can see the layout etc dont post confidental information
it will also get you a much better response.


To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.



Chris

Sorry guys - quick on the draw!Timesheet_aa.xlsx

With your sample file, are you looking for

D7 =MID(D5,FIND("(",D5)+1,FIND(")",D5)-FIND("(",D5)-1)

If not, how about showing a sample of what you expect.

The expected result is 21:00-17:00 = 4 hours?
If yes, in D7:
=MID(D5,LEN(D5)-4,2)-MID(D5,LEN(D5)-9,2)
Hope this help

IMO, the easiest and cleanest solution would be, in column B (Sheet18) right next to the shift, write in the number of hours.

Then use a vlookup (Sheet2) to return the hours.

=VLOOKUP(D5,Sheet18!$A$4:$B$17,2,0)

Now if you want the word hours to show up, on the cell with the vlookup use a custom format of 0" Hours"

Doing it this way will allow you to add up hours later if need be since they are numbers.

If you find it inconvernience in helper columns/sheet/area,
try this formula in D7:

Please Login or Register  to view this content.

I just found the shorter:

Please Login or Register  to view this content.

@jeffreybrown

Thats perfect! Thank you very much - this works out great! Except I'm still getting 9 hours when it should read 8.5

You need to set the decimals for the cell.

If you click on the 9 and look in the formula bar you should see 8.5