View Full Version : Cut away days to have only hours : 45841.12 -> 0.12

03-23-2016, 08:44 AM

I need to copy cells with vba. One cell has the format with days, the other cell has only hours. Obviously when i compare them, the cell with days will be bigger. Is there a simple trick to cut away the days ?

I use this code:

If tsm(s3).depart < (tsm(s3).fixedtime + WorksheetFunction.RoundDown(tsm(s3).depart, 0)) Then

I managed to do this by adding the days, to the figure without. I would like to keep it more simple. If there is a possibility please let me know.

Thank you

03-23-2016, 01:09 PM
Dates in Excel are number of whole and fractional days since Jan 1, 1900

So number of days to the left of the decimal and parts of a day (i.e. time) to the right of the decimal

Sub test()
Dim d As Date

d = Now

MsgBox Format(d, "yyyy-mm-dd hh:mm:ss")

MsgBox Format(d - Int(d), "hh:mm:ss")

End Sub

BUT there is a lot of precision (more decimals) that you might need to address. For example the Now above is really = 42452.6735185185. This means that the 'seconds' might look the same, but could be different in the 8th place. This might be another option

Option Explicit
Sub test()
Dim WithDays As Date, WithoutDays As Date

WithDays = Now
WithoutDays = WithDays - Int(WithDays) + 0.000001

If Format(WithDays, "hh:mm:ss") = Format(WithoutDays, "hh:mm:ss") Then
MsgBox "Same"
End If
End Sub