Macro to change 5.00 etc to 24 hour clock if the result is a minus?

[tonywatsonhelp]

Hi everyone,
I think I need a macro because of the size of the document a Formula minght be able to do this but would not run well on such a large sheet.

So that out the way heres the problem

	N	O	P	Q	R	S	T	U	V
1	Start	End	Hours	Start	End	Hours	Start	End	Hours
2	9.00	11.00	2	9.00	2.00	-7	9.00	14.00	5

<tbody>
</tbody>

Ok so in this example QRS are wrong but as you can imagine it could be any of them,

this is quite complex so I'll plan it out the best I can

the sheet hold a Time sheet filled out each day by one person,
the sheet is set to work on the 24 hour clock.

Sometime when filling out the sheet they will put start and end times both as 12 hour clock so I get an error of minus like start 9.00 end 5.00 =-4.00
I've tried several way to stop this error but its still happening.

what I'd like is this:

When a end time is entered if the Hours = a minus figure then give me a message box asking me if I meant the 24 hour end time with a yes no if yes insert 24 hour time if no leave as is
so example from above table, when I typed into cell R2 2.00 it would see the result is -7 so message box pops up and says "Did you mean to type 14.00 ?" yes /no

size of range

the sheet is calendared for over 2 years so it would be hard to tell it what cells to look at for this as a trigger but the "hours" Columns start at "P" and are every third column and the "End" start at "O"

rows are 11 to 300

I realise this is complex and if there is a better way to do it let me know.

but I really need this sorted out so please help if you can

Tony

 

[CalcSux78]

Hey Tony!

Here's a snippet from one of my other projects that may be helpful. The code has been modified for your scenario, but is designed to treat your start/end columns as a time value (9:00 instead of 9.00). The sweet thing is it will switch a decimal number to a time value. Test it out by entering 2:30 PM as "2.3" and see what happens.

It's a sheet level macro designed to launch on the sheet change event. If the column header (row 1) label is "Start" or "End" the calculation is performed.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cel As Range
Dim dTime As Double
Dim hr As Double
Dim min As Double


Select Case VarType(Target)
    Case 2 To 7
    Case 14
    Case Else
        Exit Sub
End Select


For Each cel In Target.Cells
    Select Case Trim(Cells(1, Target.Column))
        Case "Start", "End"
            If cel.Value > 1 Then
                With WorksheetFunction
                    hr = .RoundDown(cel.Value, 0)
                    min = .Round((cel.Value - hr) * 100, 0) / 60
                    dTime = (hr + min) / 24
                End With
                cel.Value = Format(dTime, "h:mm AM/PM")
            ElseIf cel.Value < 0.26 And cel.Value > 0 Then
                cel.Value = cel.Value + 0.5
            End If
        Case Else
    End Select
Next cel
End Sub

Excel 2010

	N	O	P	Q	R	S	T	U	V
1	Start	End	Hours	Start	End	Hours	Start	End	Hours
2	9:00 AM	11:00 AM	2.00	9:00 AM	2:00 PM	5.00	9:00 AM	2:00 PM	5.00

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula P2 =IF(O2>=N2,(O2-N2)*24,"Error") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[tonywatsonhelp]

Thanks CalcSux78, this will be a great help, looks amazing bit of code so thanks for your help

Tony