# Another Time Sheet Puzzle

#### meighkee

##### New Member
Greetings,

I'm developing a work schedule and would like to use military time.

My first task is to input the "in" and "out" times in military time, noting I will need to use these numbers for calculations. I am trying to avoid using the colon as the extra keystrokes, times 50+ employees, takes up a lot of time.
My second task is to calculate the total hours for the week and display it in decimal format. For example, having the total hours - 4530 displayed as 45.5.

A simple example of this effort is:
In Cell A1 I will place the start time - 0600.
In Cell A2 I will place the end time - 1430.
In Cell A3 I wish to subtract the times - 1430 from 0600 giving me the total hours worked as 830. I would like this number to display 8.5 rather than 830.

I have gone round and round with this and have accomplished portions of it but can't seem to find the right combination of formulas to accomplish everything.

Thank you,
Michael

### Excel Facts

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

#### lenze

##### Legend
Hi and welcome to the board!!
Try
Code:
``=DOLLARDE((A3-A2)*.01,60)``
Analysis ToolPak required

lenze

#### Special-K99

##### Well-known Member
Not to put a downer on Lenze's hard work but

DOLLARDE works in the example given, ie 0600 to 1430 equals 8.5 hours
but not when the finish time hits the next hour, e.g. 0630 to 1500 DOLLARDE produces 9.16667 it should still be 8.5

#### lenze

##### Legend
Good Catch K-99
Try this
Code:
``=DOLLARDE((A3*0.01)-(A2*0.01),60)``
lenze

#### lenze

##### Legend
The above doesn't work either, but this does
Code:
``=DOLLARDE((A3*0.01),60)-DOLLARDE((A2*0.01),60)``
lenze

#### Osvaldo Palmeiro

##### Well-known Member
And what about this:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com ffice ffice" /><o ></o >
<o ></o >
=(A2-A1)*24 this cell formated as: Format / cells / Category = General<o ></o >

#### lenze

##### Legend
And what about this:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com ffice ffice" /><o ></o >
<o ></o >
=(A2-A1)*24 this cell formated as: Format / cells / Category = General<o ></o >
This will not work unless he enters his times in time format, which he is not doing.

lenze

#### IvenBach

##### Board Regular
Would =INT((B1-A1)*0.01)+ROUND((((B1-A1)*0.01-INT((B1-A1)*0.01)))*100/60,2) be a viable option without the analysis toolpak?

#### meighkee

##### New Member
Thank you all. I really appreciate the help. It appears as though the formula Lenze recommended is working quite well. I've experimented with a variety of times and so far so good.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com ffice ffice" /><o ></o >
<o > </o >
Of course, that example was for one day out of the week. I'm going to try now and use the same formula string to include the remaining 6 days. It looks as though I'm going to have one reeeeallllly long formula.<o ></o >
<o > </o >
If there's a simpler method, I'm all ears. <o ></o >
<o > </o >
Example: =DOLLARDE((A3*0.01),60)-DOLLARDE((A2*0.01),60)+DOLLARDE((A5*0.01),60)-DOLLARDE((A4*0.01),60)+..., etc<o ></o >
<o > </o >
Where A3 & A2 represent Monday's out and in times, A5 & A4 will represent Tuesday's out and in times, etc.<o ></o >
<o > </o >
Thanks again very much,<o ></o >
Michael

#### lenze

##### Legend
Just a suggestion. You can enter your times as a decimal. (5.30 for 5 hrs 30 min.)
This will eliminate the need to multiply by 0.01
Also, you can convert your times on the fly (As they are entered)
Code:
``````Option Explicit

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Count > 1 Then Exit Sub
If Target.Column <> 2 Then Exit Sub
If Target.Row < 2 Then Exit Sub
If Application.WorksheetFunction.IsNumber(Target) Then
Application.EnableEvents = False
Target = Evaluate("=dollarde(" & Target & ",60)")
Application.EnableEvents = True
Else:
Application.EnableEvents = False
MsgBox "Invalid Entry. Entry will be cleared", vbOKOnly, "Opps"
Application.Undo
Application.EnableEvents = True
End If
End Sub``````
See my article here
http://vbaexpress.com/kb/getarticle.php?kb_id=882
lenze

Last edited: