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

[apwdweb]

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

 

[SIXTH SENSE]

hi!
is this what you mean?

Book1
	A	B	C	D	E	F
1	day	month	year	date	daysback	thedate90daysbackbefore12-10-2003
2	10	12	2003	December10,2003	90	September11,2003
Sheet2

 

[Chitosunday]

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

 

[white6174]

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

 

[apwdweb]

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

 

[Chitosunday]

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)

 

[apwdweb]

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.

 

[Chitosunday]

'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)

 

[Chitosunday]

or use this

a = 15
b = 1
c = 2004
ActiveCell = Format(Application.ExecuteExcel4Macro("workday(""" & DateSerial(c, b, a) & """,-90)"), "mm-dd-yy")