String to date object? - go back a custom number of days? :)

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: String to date object? - go back a custom number of days? :)

  1. #1
    Board Regular
    Join Date
    Aug 2002
    Posts
    86
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default String to date object? - go back a custom number of days? :)

     
    Hi All,

    I'm stumped and wonder if anyone can help me figure this one out

    I have a custom date stored via three integer variables a,b,c

    a = 10 '10 Dec
    b = 12 '12th
    c = 2003 '2003 year

    Suppose i need to convert this date to a date that is exactly 30 days back or 90 days or better yet a Custom number of days back.

    I was wondering if there is a good way to do this accurately to reflect leap years and the number of days for each month.

    Would appreciate your input.
    Thanks in advance
    Jennifer

  2. #2
    Board Regular SIXTH SENSE's Avatar
    Join Date
    Oct 2003
    Posts
    1,883
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    hi!
    is this what you mean?

    ******** ******************** ************************************************************************>
    Microsoft Excel - Book1___Running: xl2000 : OS = Windows XP
    (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
    1
    daymonthyeardatedays backthe date 90 days back before 12- 10- 2003
    2
    10122003December 10, 200390September 11, 2003
    Sheet2

    [HtmlMaker 2.42] 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.
    There is always a better way!!

  3. #3
    Board Regular Chitosunday's Avatar
    Join Date
    Jul 2003
    Posts
    1,017
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Using VBA or macro for date less 30 days

    a = 10
    b = 12
    c = 2003
    MsgBox DateSerial(c, b, a) - 30

    using formula
    =date(c,b,a)-30
    Mr Young at Heart - Learning is like chasing the wind, it's boundless.

  4. #4
    Board Regular
    Join Date
    May 2002
    Location
    CALIFORNIA
    Posts
    137
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: String to date object? - go back a custom number of days

    heres something to try
    just an example I made up

    hope it helps
    steve w


    Private Sub CommandButton1_Click()
    Dim a As Variant
    Dim b As Variant
    Dim c As Variant


    a = 10 '10 Dec
    b = 12 '12th
    c = 2003 '2003 year

    TextBox1.Value = DateSerial(c, b, a)

    TextBox2.Value = DateSerial(c, b, a) - TextBox3.Value 'textbox3 has value you want to subtract

    End Sub

  5. #5
    Board Regular
    Join Date
    Aug 2002
    Posts
    86
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: String to date object? - go back a custom number of days

    Great! thank you!

    now what if i want to convert the date back to a string after i subtract (go back) 90 days?

    Thanks

  6. #6
    Board Regular Chitosunday's Avatar
    Join Date
    Jul 2003
    Posts
    1,017
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    a = 10
    b = 12
    c = 2003
    ActiveCell = "'" & DateSerial(c, b, a) - 90
    a = Day(DateSerial(c, b, a) - 90)
    b = Month(DateSerial(c, b, a) - 90)
    c = Year(DateSerial(c, b, a) - 90)
    Mr Young at Heart - Learning is like chasing the wind, it's boundless.

  7. #7
    Board Regular
    Join Date
    Aug 2002
    Posts
    86
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: String to date object? - go back a custom number of days

    chitosunday

    Thanks for your help. DO you know if it is possible to count 90 days backwards excluding weekends?

    Thanks.

  8. #8
    Board Regular Chitosunday's Avatar
    Join Date
    Jul 2003
    Posts
    1,017
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    'use the workday function
    a = 15
    b = 1
    c = 2004
    ActiveCell.Formula = "=workday(" & """" & DateSerial(c, b, a) & """" & ",-90)"
    ActiveCell.Value = "'" & ActiveCell.Value
    a = Day(ActiveCell)
    b = Month(ActiveCell)
    c = Year(ActiveCell)
    Mr Young at Heart - Learning is like chasing the wind, it's boundless.

  9. #9
    Board Regular Chitosunday's Avatar
    Join Date
    Jul 2003
    Posts
    1,017
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    or use this

    a = 15
    b = 1
    c = 2004
    ActiveCell = Format(Application.ExecuteExcel4Macro("workday(""" & DateSerial(c, b, a) & """,-90)"), "mm-dd-yy")
    Mr Young at Heart - Learning is like chasing the wind, it's boundless.

User Tag List

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