Issuing retrieving an "hour value" from worksheet using VBA

[MarkOz]

Hi all

Fist time here and hoping someone can help. I've just spent hours googling and have had no luck.

I have an Excel worksheet which contains cumulative hours worked.

I have a VBA form where user enters next shift information.

I need to pull the current shift total into VBA (I have to display the current value - which may be zero or greater - as well as the new value when I add the shift duration). The issue is that I am able to format the cell show it displays as an actual "hour value" using [h]:mm format and it's all good where the shift total is under 24 hours (probably 23:39)

However, when the value is 24 or more when I pull it into my module using the following code:

dteCurrentShiftTotal1 = wsCurrent.Cells(UseRow, intDay1Col).Value

[Declarations - dteCurrentShifttotal1 as date, wsCurrent as worksheet, UseRow and intDay1Col are Integers]

... it comes in as a date/time - e.g. if the display value is 27:00 in the cell, dteCurrentShiftTotal1 is assigned a value of 1/01/1900 3:00:00 AM

The issue is I don't know how to determine if it is meant to be 3:00 hours shift total, 27:00 or even 51:00.

Can someone advise me please?

Cheers

mark

[p45cal]

That date is 27 hours. You don't need to do anything special.
If you CDbl its value it will come to 1.125 (days)
To demonstrate:

Sub blah()
Set wsCurrent = ActiveSheet
UseRow = 4: intDay1Col = 6 'cell F4 contains: 1/01/1900 3:00:00 AM
dteCurrentShiftTotal1 = wsCurrent.Cells(UseRow, intDay1Col).Value
MsgBox dteCurrentShiftTotal1 'this may surprise you! Ignore it.

y = CDbl(dteCurrentShiftTotal1) '1.125 (days), x24 = 27 hours.
MsgBox y

Range("H4").Value = Empty
Range("H4").NumberFormat = "General"
Range("H4") = dteCurrentShiftTotal1 'look at H4 on the sheet
Range("H4").NumberFormat = "[hh]:mm" 'look again at H4 on the sheet

Z = Format(dteCurrentShiftTotal1, "[hh]:mm") 'doesn't work
MsgBox Z

z1 = Application.WorksheetFunction.Text(dteCurrentShiftTotal1, "[hh]:mm") 'using the sheetfunction does.
MsgBox z1
End Sub

[MarkOz]

Gday p45cal

Thank you for your quick reply and the solution.

Using CDbl (dteCurrentShiftTotal1) gives me a result I can work with. Then after I add the next shift I can use Integer math and CStr to turn it back into hh:mm format in the worksheet.

Really appreciate your input

Cheers

Mark