Current date plus time VBA

Question

I want to create a button which will do the following. - Retrieve the current date and time --> - This date and time plus the time in (O2) --> - In the range of cells (L2:L11) needs to be displayed if the current time plus the time in cell O2 is less or more than the date and time in the cell next to it (Range M2:M11). The cell L2 will be compared with M2, L3 with M3 etc.

On any current time, you will be able to click on the button and it will calculate if it is on time or too late.

I know how to get the current date but can't get any further.

Click here for the image with the cells

Sub OnTime()


With Range("L2:L11")
   .Value = Now()
   .NumberFormat = "mm/dd/yyyy h:mm:ss AM/PM"
End With

End Sub

What is your question? What prevents you to do so if you want? — FunThomas
What region format are you using? dd-mm-yy or mm-dd-yy? Also, any reason why you can't use worksheet formulas? You can use NOW function in both VBA and spreadsheet to get current DateTime — QHarr
As Raunak has posted and i mentioned above, can you not simply do with formulas in the spreadsheet and hit F9 to recalculate; or have a button trigger a worksheet calculate event (more efficient to target the actual sheet than the workbook) — QHarr

Raunak Thomas Raunak Thomas · Accepted Answer · 2017-10-20T13:20:31

Why don't you simply do this

In cell O2 type "=now"
in cell L2 type "=IF(B1<D1,"Ontime", "Late")"

After that each time you refresh the formulaes it will be recalculated. Do that by linking this macro to a button

Sub Refresh_formulaes()
    Calculate
End Sub

Current date plus time VBA

1 Answers