Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Dates help
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 -- Sami82 ------------------------------------------------------------------------ Sami82's Profile: http://www.excelforum.com/member.php...o&userid=27111 View this thread: http://www.excelforum.com/showthread...hreadid=466700 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Dates help
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 "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 -- Sami82 ------------------------------------------------------------------------ Sami82's Profile: http://www.excelforum.com/member.php...o&userid=27111 View this thread: http://www.excelforum.com/showthread...hreadid=466700 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Dates help
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 "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 -- Sami82 ------------------------------------------------------------------------ Sami82's Profile: http://www.excelforum.com/member.php...o&userid=27111 View this thread: http://www.excelforum.com/showthread...hreadid=466700 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Dates help
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-Žex ------------------------------------------------------------------------ T-Žex's Profile: http://www.excelforum.com/member.php...o&userid=26572 View this thread: http://www.excelforum.com/showthread...hreadid=466700 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Linking computer dates (time) to spreadsheet dates that have formu | Excel Worksheet Functions | |||
how do I sort a column of random dates into Consecutive dates | Excel Worksheet Functions | |||
Toggle a range of Julian dates to Gregorian Dates and Back | Excel Programming | |||
Identifying unique dates in a range of cells containing dates... | Excel Discussion (Misc queries) | |||
Identifying unique dates within a range of cells containing dates | Excel Discussion (Misc queries) |