VBA Dates help

[Sami82]

Hi All,

I have written the following code to display the dates on a row which
occur between two dates (sdate and edate). These are all mondays. I am
having two problems with this.

1. I want the form to work out the number of weeks between these two
dates (currently you have to type in the amount of weeks). And then i
want this number to automatically update on the form when the end date
has been entered.

2. The dates appear on the excel sheet as 1900 even when i have entered
the dates in the form as dd/mm/yyyy.

Thanks for the help.

rivate Sub PromoDatesOK_Click()

Dim sdate As Date
Dim edate As Date
Dim cnt As Integer
Dim wknumtot As Integer

'ActiveWorkbook.Sheets("Claim").Activate
Range("a1").Select
wknumtot = cboweeks.Value
ActiveCell.Value = wknumtot

sdate = Val(sdatebox.Value) - 1
edate = edatebox.Value
Range("d6").Select
ActiveCell.Value = sdate

cnt = 1

Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(0, 1).Select
ActiveCell = ActiveCell.Offset(0, -1) + 7
cnt = cnt + 1
End If
Loop Until cnt = wknumtot

Unload Me


End Sub

 

[Robert Mulroney]

You need to cast your date string into the date format. Then it's possible
to do simple artimatic on them. The date format is just a type "Long" which
repersents the number of seconds from or until some arbitarty date (I can't
remember when).

Heres a sub that works our my exact age in weeks:

Sub myAgeInWeeks()

Dim s As Date
Dim e As Date

'cast my string as a date
s = CDate("14/03/1979")
'the current time
e = Now()

'Yes I'm Australian, we put the date the right way round.
Debug.Print "The today's date is " & Day(e) & "/" & Month(e) & "/" & _
Year(e) & " at " & Hour(e) & ":" & Minute(e)
Debug.Print "I am " & (s - e) / 7 & " weeks old."

End Sub

Problem two:

Use the format function to reformat string how you want them to look.

Format(now(), "dddd, mmm d yyyy")



Hope this helps

- Rm

 

[Patrick Molloy]

try this in a standard module...th esub is public, so that you can call it
from a form if need be...

Option Explicit
Sub testit()
SetDates #1/1/2005#, #7/15/2005#
End Sub

Sub SetDates(sDate As Date, eDate As Date)

Dim index As Long

Do While Weekday(sDate, vbMonday) <> 1
sDate = sDate + 1
Loop

Do Until sDate >= eDate
index = index + 1
With Cells(index, 1)
.Value = sDate
.NumberFormat = "dd/mm/yy"
End With
sDate = sDate + 7
Loop

End Sub

 

[T-®ex]

Hi Sami81!

If you'd like to count the number of weeks between 2 dates, you can use
the DateDiff function:

DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]])

Dim NumWeeks as Integer
NumWeeks = DateDiff(*"w"*, sdatebox.Value, edatebox.Value, vbSunday)

If you want the number of weeks to be automatically calculated after
you enter the end date, you can put the code above in an AfterUpdate
event of your edatebox (TextBox?). Just make NumWeeks (or *wknumtot*)
be accessible in the entire module (so don't declare it
[NumWeeks/wknumtot] local to a sub or func).

Please read more about the DateDiff function in VBA Help.

About your dates appearing as 19xx, I think the culprit is this line:

sdate = *Val*(sdatebox.Value) - 1

Val, according to VBA Help:

Returns the numbers contained in a string as a numeric value of
appropriate type...The Val function stops reading the string at the
first character it can't recognize as part of a number...

If you're trying to convert the text in sdatebox to a date, Val is the
func to use. You can use DateValue instead.

Try using this:


Code:
--------------------
sdate = DateValue(sdatebox.Value) - 1
edate = DateValue(edatebox.Value)

Dim CurrentCell As Range
Set CurrentCell = Range("D6")

CurrentCell.Value = sdate

cnt = 1

Do
If Not IsEmpty(CurrentCell) Then
CurrentCell.Offset(0, 1).Value = CurrentCell.Value + 7
Set CurrentCell = CurrentCell.Offset(0, 1)
cnt = cnt + 1
End If
Loop Until cnt = wknumtot