Determining 'time worked' in a cell

[LindaEPI]

I need to create an excel spreadsheet for workers to use a pull down menu in one cell for START TIME, another for END TIME and then a third column that determines how many hours worked.

example: 7:30 a.m - 8:30 a.m. = 1 hr.
Can this be done in excel?

 

[AnotherHiggins]

=B1-A1

That's it!

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[ThomasLafferty]

Also, to get the dropdown menu, you will need a list of values.

It's probably easiest to put them on an unused area of your spreadsheet which won't be printed.

For my example, I will use cell K1 as my starting point since with default margin settings, it is the first cell on page 2 of a new sheet.

Enter the following values:

K1     12:00 AM
K2     12:01 AM

Select K1:K2 and Autofill those values down through K1440. It will take a moment or so, but it will increment your values by 1 minute as you fill downward. When you are done, you will have accounted for a 24 hour period in 1 minute increments.

Leave the range selected, and click the Name box (it is to the immediate left of the formula bar). Type the name ClockTimes [!](do not space between Clock and Times)[/!] and press the enter key. This named range can now be used to give you a valid dropdown list of acceptable times anywhere in your workbook.

Here's how:
1. Select the cell(s) you want the dropdown to appear in. For my example, I am using [!]A1 and B1.[/!]
2. Click Data>Validation (or Click the Data ribbon and select Data Validation).
3. In the dialog which appears, select the [!]Allow[/!] dropdown and choose [!]List[/!].
4. In the [!]Source[/!] box, type [!]=ClockTimes[!]
5. Click OK.

NOTE: you may have to format the cells in order to get the times to display the way you want them.

6. Enter the following formula in C1:

=(A1-B1)*24

7. Format C1 with a comma separator (accounting no symbol)
8. Format A1:B1 and ClockTimes as Time (you choose the format)

Voila!

Hope that helps.

Tom

Born once die twice; born twice die once.

 

[ThomasLafferty]

Also, if you are using this as some sort of payroll clock, you will also probably need to account for lunch. To do this, you will need dropdowns in A11.

Put the following titles in A1:E1:

         A         B          C            D          E
1    Time In    Time Out   Lunch In    Lunch Out   Total Hours

Put dropdowns in A22

Enter this formula in E2:

=(D2-A2)*24-(C2-B2)*24

Enjoy!

Tom



Born once die twice; born twice die once.

 

[LindaEPI]

Thank you, I think that is what I was wanting. I was working on the drop down and I couldn't get the times right.

Is there a way type in something in the drop down if what you want isn't there? In this case, I'm referring to another drop-down I will be making, one for the names.

Again, thanks for your help.

 

[herkiefan]

Would this work if the schedule of hours crosses 12am?

 

[SkipVought]

herkiefan,

It depends if you have entered your next day time correctly.

Why do Dates and Times seem to be so much trouble? faq68-5827

Please post any NEW QUESTIONS in a new thread.

Skip,

_[red][/red]