Another Time Sheet Puzzle

meighkee

New Member
Joined
Sep 3, 2008
Messages
39
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
 

Some videos you may like

Excel Facts

Links? Where??
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
Joined
Feb 18, 2002
Messages
13,690
Hi and welcome to the board!!
Try
Code:
=DOLLARDE((A3-A2)*.01,60)
Analysis ToolPak required

lenze
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,342
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
Joined
Feb 18, 2002
Messages
13,690
Good Catch K-99
Try this
Code:
=DOLLARDE((A3*0.01)-(A2*0.01),60)
lenze
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
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
Joined
Feb 24, 2009
Messages
559
Office Version
365
Platform
Windows
And what about this:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
=(A2-A1)*24 this cell formated as: Format / cells / Category = General<o:p></o:p>
 

lenze

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

lenze
 

IvenBach

Board Regular
Joined
May 31, 2008
Messages
212
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
Joined
Sep 3, 2008
Messages
39
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:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p> </o:p>
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:p></o:p>
<o:p> </o:p>
If there's a simpler method, I'm all ears. :)<o:p></o:p>
<o:p> </o:p>
Example: =DOLLARDE((A3*0.01),60)-DOLLARDE((A2*0.01),60)+DOLLARDE((A5*0.01),60)-DOLLARDE((A4*0.01),60)+..., etc<o:p></o:p>
<o:p> </o:p>
Where A3 & A2 represent Monday's out and in times, A5 & A4 will represent Tuesday's out and in times, etc.<o:p></o:p>
<o:p> </o:p>
Thanks again very much,<o:p></o:p>
Michael
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
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:

Watch MrExcel Video

Forum statistics

Threads
1,099,521
Messages
5,469,136
Members
406,638
Latest member
Jack_Johnson

This Week's Hot Topics

Top