PDA

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



lks55
03-23-2016, 08:44 AM
Hi,

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

Paul_Hossler
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