excelvbatime

Changing cells with date values to match current date, but keep original time

enter image description here

Afternoon all! Got a slight little query for you to ponder over.

I currently have 2 tables with the above times (which are dynamic and change often). The left table pulls data on times between 08:00 and 18:30, and the right pulls times between 18:30 and 4:30 the next day including the dates attached to those times. When i open my workbook, The times are updated on WorkBook_Open through VBA, I need to update the times shown so that the values keep the time, but update the date to the current day, so if the data pulled is 01/12/2023 12:30, then the cell updates to 04/12/2023 12:30.....

I use the formula

cel.Formula = "=TEXT(" & cel & ",""hh:mm"") + Today()"

which seems to work on all times that fall on the current day. However the issue that I'm facing, is, for example in the right hand table... the 00:15 values are for the next day, so the above formula runs through and converts it to the current day time... for EG:

01/01/1990 21:30 02/01/1990 00:15

But running the formula will change it to:

04/12/2023 21:30 04/12/23023 00:15

Whereas the rightmost value should be 05/12/23023 00:15

If any of that makes sense then any help would be appreciated, working with Times in Excel is definitely one of my weaker skills

Many Thanks :)

Solution 
  • Option Explicit

Sub Demo1()
    Dim c As Range, iOffset As Long
    Const FIRST_CELL = "A1"
    iOffset = Date - CDate(Format(Range(FIRST_CELL), "MM/dd/yyyy"))
    Debug.Print iOffset
    For Each c In Range("A1").CurrentRegion
        c.Value = c.Value + iOffset
    Next
End Sub

Sub Demo2()
    Dim c As Range, iOffset As Long
    Const FIRST_CELL = "A1"
    Const TEMP_CELL = "E1"
    Range(TEMP_CELL) = Date - CDate(Format(Range(FIRST_CELL), "MM/dd/yyyy"))
    Range(TEMP_CELL).Copy
    Range("A1").CurrentRegion.PasteSpecial xlPasteValues, xlPasteSpecialOperationAdd
    Application.CutCopyMode = xlCopy
    Range(TEMP_CELL).ClearContents
End Sub

    enter image description here

    If you change the date layout a little bit, updating will be eaiser.

    enter image description here