Forum Discussion

Andrew_Hinson's avatar
Andrew_Hinson
Brass Contributor
Apr 18, 2023

VBA - Insert current time PLUS one minute

Hi everyone,

 

I'm really sorry; I keep asking questions on here but never actually respond to anyone else's queries. I'm still an absolute amateur and can't help myself let alone anyone else. So I really appreciate everyone's help here. I hope I can slowly move out of the amateur status! I know this is a read, but someone please please help 🙂

 

I am trying to create a macro that will insert the current time and then add one minute to it into the selected cell. So for example, if the time is 19:30, I click into a cell and run the macro, leaving 19:31 in that selected cell.

 

I am setting up a timesheet with numerous boxes in which the user can write the task they are carrying out and insert a start time and an end time.  I know how to do a macro for this so inserting the current time isn't an issue, it's the time plus one minute I cannot do. I have inserted a button called Insert Time and allocated my recorded macro of inserting the current time to this.

 

So, as it is now, they click in the start or end time box, then click the macro and the current time is inserted. So we can see the time they started a task and the time they finished. However, when they move from one task to another, I want them to be able to click in the new start box, and have click another macro enabled button (a separate one) which will add the current time again, but add on one extra minute this time. So there will be two macro buttons. Insert Time and Insert Time Plus One.

 

This way, the new task isn't using the same minute as the one before. For example if we finish task 1 at 19:30 and put that as the end time using the first macro, and then some seconds later click into the next task start box and run the Time Plus One macro, it would put it as 19:31 rather than 19:30.

 

I appreciate this will not be entirely and exactly accurate on time, but I just want tasks separated in terms of time.  

 

I have tried recording macros of Ctrl+Shift+; (to insert the time like the other macro does) but I cannot then add an equals sign and anything else to try and get it to +1. I have seen online that the formula needs an actual cell ref and so would be =A2(input cell)+1/1440. I have tried adding a helper cell in and recording a macro that inserts the time into this helper cell, then moves back up to where I want the time + 1 to be and links to that cell that has the time, so it is a cell reference in the formula A2+1440 rather than just the time. But that didn't work either!

 

If anyone can understand what I am asking here, please can you help me with this!

 

Thank you very much for your time. I know that's a read! 🙂

Macros and VBA
    • Andrew_Hinson's avatar
      Andrew_Hinson
      Brass Contributor
      Apr 18, 2023

      HansVogelaar thank you so much, again!

       

      I tweaked the code a little (I have no idea with VBA! I just fiddled with what looked right such as the name of the macro) and I have it working!

       

      Only thing is that it inputs the date as well as the time? I need it to just enter the time (plus 1) as hh:mm

       

      I tried changing the word date to time but it just kept having a run error?

       

      Can you advise what I need to do please?

       

      Massive thank you.

       

       

       

       

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Apr 18, 2023

        Andrew_Hinson 

        One option is to format the cell as hh:mm. The date part will still be present, but not shown in the cell:

        Sub TimePlusOne
    On Error Resume Next
    With ActiveCell
        .Value = DateAdd("n", 1, Now)
        .NumberFormat = "hh:mm"
    End With
End Sub

        If you prefer to enter only the time in the cell:

        Sub TimePlusOne()
    Dim t As Date
    On Error Resume Next
    t = Now
    With ActiveCell
        .Value = DateAdd("n", 1, t - Int(t))
        .NumberFormat = "hh:mm"
    End With
End Sub

Share

Resources