VBA Formula Not Removing 30 Minutes From Time

[Ark68]

I am looking for some help with the following vba formula ...

mm2 = DateAdd("h", -0.5, sh.Cells(rw, 6))

I am trying to get a time value (mm2) that is 30 minutes less the time value in sh.cells(rw,6). This code isn't doing it.

The value in sh.Cells(rw,6) = 46061.8541666667 (2023-05-15 8:30 PM)
The resulting value of mm2 = 2023-05-15 8:30 PM

I was hoping to get mm2 = 2023-05-15 8:00 PM

 

[RudRud]

While waiting for other experts, I will share my thought here.

Book1
	B	C
1	2026-02-08 20:30	2026-02-08 20:00
Sheet3

Sub minutetest()
time1 = CDate([b1].Value)

mm2 = time1 - TimeSerial(0, 30, 0)

[c1].Value = mm2
End Sub

how about
mm2 =sh.Cells(rw, 6) - TimeSerial(0, 30, 0)

 

[Peter_SSs]

.. or

mm2 = sh.Cells(rw, 6) - 1 / 48

 

[johnnyL]

@Ark68 The date and time you are expecting are not going to happen with the value that you posted.

 

[Peter_SSs]

@Ark68 The date and time you are expecting are not going to happen with the value that you posted.

But most likely just a typo of a 6 instead of a 5.

 

[Tetra201]

... This code isn't doing it...

That's because the description of the DateAdd Function says: If number isn't a Long value, it is rounded to the nearest whole number before being evaluated.

The following does work:

mm2 = DateAdd("n", -30, sh.Cells(rw, 6))