Issuing retrieving an "hour value" from worksheet using VBA

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

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:

Code:

---

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

---

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