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
--------------------
Hope this helps...
Sami82 Wrote:
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
--
T-容x
------------------------------------------------------------------------
T-容x's Profile:
http://www.excelforum.com/member.php...o&userid=26572
View this thread:
http://www.excelforum.com/showthread...hreadid=466700