Results 1 to 8 of 8

Adding minutes to a date in VBA

This is a discussion on Adding minutes to a date in VBA within the Excel Questions forums, part of the Question Forums category; I'm trying to write a function which takes a date and time and a number of minutes, adds to minutes ...

  1. #1
    Board Regular
    Join Date
    Feb 2003
    Location
    Tampa, FL
    Posts
    159

    Default Adding minutes to a date in VBA

    I'm trying to write a function which takes a date and time and a number of minutes, adds to minutes to the date/time and returns the new date/time.

    For example, if I gave it "2/23/04", "22:00" and 150 minutes, it should return "2/24/04 00:30".

    Anyone know how to do that?

  2. #2
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454

    Default Re: Adding minutes to a date in VBA

    How about ...

    ******** ******************** ************************************************************************>
    Microsoft Excel - y040223h1a.xls___Running: xl97 : OS = Windows 98
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    E
    F
    2
    *02/23/20042215002/24/2004*00*30*
    Sheet4*

    [HtmlMaker 2.20] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


    =b2+c2/24+d2/1440

    would this do -- or do you still want a UDF?
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  3. #3
    Board Regular
    Join Date
    Feb 2003
    Location
    Tampa, FL
    Posts
    159

    Default Re: Adding minutes to a date in VBA

    It needs to be in VBA.

  4. #4
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454

    Default Re: Adding minutes to a date in VBA

    OK ...
    Code:
    Public Function y_OHG(d As Range, h As Range, m As Range)
        y_OHG = d + h / 24 + m / 1440
    End Function
    ******** ******************** ************************************************************************>
    Microsoft Excel - y040223h1a.xls___Running: xl97 : OS = Windows 98
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    E
    F
    2
    *02/23/20042215002/24/2004*00*30*
    Sheet4*

    [HtmlMaker 2.20] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


    Dose it help?
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  5. #5
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454

    Default Re: Adding minutes to a date in VBA

    And if you want to enter the date, the hours, and the minutes as arguments directly into the function, then you can use ...
    Code:
    Public Function y_OHG1(d As Date, h As Long, m As Long)
        y_OHG1 = (d + h / 24 + m / 1440)
    End Function
    ******** ******************** ************************************************************************>
    Microsoft Excel - y040223h1a.xls___Running: xl97 : OS = Windows 98
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    E
    F
    5
    ****02/24/2004*00*30*
    Sheet4*

    [HtmlMaker 2.20] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  6. #6
    Board Regular
    Join Date
    Feb 2003
    Location
    Tampa, FL
    Posts
    159

    Default Re: Adding minutes to a date in VBA

    I'm only using VBA...not a worksheet. Here's what I have and it doesn't work:

    Code:
    Sub test()
        MsgBox y_OHG1("2/23/2004", "23:00", 120)
    End Sub
    Public Function y_OHG1(d As Date, h As Long, m As Long)
        y_OHG1 = (d + h / 24 + m / 1440)
    End Function

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,950

    Default Re: Adding minutes to a date in VBA

    Take a look at the TimeSerial function:

    Code:
    ?Now, Now + TimeSerial(0, 10, 0)
    2/23/2004 10:16:42 PM       2/23/2004 10:26:42 PM
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  8. #8
    Board Regular
    Join Date
    Feb 2003
    Location
    Tampa, FL
    Posts
    159

    Default Re: Adding minutes to a date in VBA

    Now, THAT'S something I can work with! I was using timeserial, but I never thought about doing now+timeserial.

    Good idea...as usual.

    Thanks!

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com