Another Time Sheet Puzzle

[meighkee]

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

 

[lenze]

Hi and welcome to the board!!
Try

=DOLLARDE((A3-A2)*.01,60)

Analysis ToolPak required

lenze

 

[Special-K99]

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]

Good Catch K-99
Try this

=DOLLARDE((A3*0.01)-(A2*0.01),60)

lenze

 

[lenze]

The above doesn't work either, but this does

=DOLLARDE((A3*0.01),60)-DOLLARDE((A2*0.01),60)

lenze

 

[Osvaldo Palmeiro]

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

 

[lenze]

And what about this:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><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]

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]

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-comfficeffice" /><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]

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)

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