Date Functions

[Gin_bee]

Need a bit of help with some date functions. I have a project that needs to do two different calc's to figure either the period end date or the total number of weeks between two dates. I'm trying to write two different public sub's in a global bas as I need to be able to call them both from various different forms within the project.

The first has to work out the end date by adding on the number of weeks entered(txt field) to the start date(maskEdBox). I can't get the DateADD function to work properly.

The other has to work out the number of weeks between the dates entered(both maskEDBox's). Again I have a problem with DateDiff function.

Anyone's whose done this sort of thing before I'd appreciate any help, It looks like it should be simple enough.....which doesn't say a lot for me!!

Thanx in advance
Gin

[Ianpbaker]

Hi gin_bee

What problems are you getting ?

Ian

[Nitro]

Hello Gin_Bee!

Not quite sure I understand your problem, but here is all of my date functions example!

Option Explicit

Private Sub cmdDateSerial_Click()
'Enter numbers which represent year,month,date to get a date
'DateSerial is use when you want to subtract or add to either the year, month, day
    Dim vAnswer As Date
    vAnswer = DateSerial(1999, 2, 12)
    MsgBox vAnswer, , "DateSerial"
    
    Dim vLastYear As Date
    vLastYear = Year(Date) - 1
    vAnswer = Format(DateSerial(vLastYear, 4, 7), "mmmm d, yyyy")
    MsgBox vAnswer, , "Format"
End Sub

Private Sub cmdDateValue_Click()
'Enter a string to get a date
'DateValue is use when you want to convert a string into a date
    Dim vAnswer As Date
    vAnswer = DateValue("November 1, 1998")
    MsgBox vAnswer, , "DateValue"
End Sub

Private Sub cmdDate_Click()
    'Use "DATE" or "Time" function to set or obtain the current time
    'Use "NOW" to obtain both the current date and time
    Date = #10/21/1998#
End Sub

Private Sub cmdDateAdd_Click()
'Add a date and a number to get a date
    Dim FirstDate As Date
    FirstDate = InputBox("Enter a date!")
    
    '"d" specifies the number of days to be added
    Dim Number As Integer
    Number = InputBox("Enter the amount of days to be added to the date you just input!")
    
    Dim str_Data As String
    str_Data = "New date: " & DateAdd("d", Number, FirstDate)
    MsgBox str_Data, , "DateAdd"
End Sub

Private Sub cmdDateDiff_Click()
'Subtract a date from a date to get a number
    Dim TheDate As Date
    TheDate = InputBox("Enter a date!")
    
    '"d" specifies the number of days to be added
    Dim str_Data As String
    str_Data = "Days from today: " & DateDiff("d", Now(), TheDate)
    MsgBox str_Data, , "DateDiff"
End Sub

Private Sub cmdDatePart_Click()
'Enter a date to get a number
'Displays which day of the year the input date falls in.
    Dim TheDate As Date
    TheDate = InputBox("Enter a date!")
    
    Dim str_Data As String
    str_Data = "Day: " & DatePart("y", TheDate)
    MsgBox str_Data, , "DatePart"
End Sub

[Gin_bee]

With the DateAdd I want to be able to calculate the End/Finish Date. The user enters a start date into a maskEDBox and then manually enters the number of weeks( via a textbox) the process is to last. At the minute I can't generate the correct end date.

Does this help any? Sorry I can't be more specific but I'm at home and havn't got the project with me.

[Ianpbaker]

Try this for size

newdate = DateAdd("ww",Cint(txtNumOfWeeksToAdd),Cdate(txtDateToADD))

That should sort ouy your problem

Hope it helps

Ian

[HeSaidJoe]

[code]
'maskedbox1 contains the starting date mm-dd-yyyy
'maskedbox2 contains the number of weeks
'in formula below (EndDay = weeks * 7 days per week)

Private Sub Command1_Click()
'varibales required for calculation
Dim StartDay As String, _
Endday As String _
DueDay as string
'fill variables
StartDay = MaskEdBox1.Text
Endday = MaskEdBox2.Text

DueDay = DateAdd("d", (Endday * 7), StartDay)
'your code goes here...msgbox for display only
MsgBox Format(DueDay, "mmm,dd,yyyy")
End Sub
[code]

[Gin_bee]

Nitro, Ian and HeSaidJoe


Thanx for the code, I'll give it another shot tomorrow at work.