Date (1 Viewer)

[deepatel70]

Can someone please help me...what i want to do is get today's date and add 5 working day to it and display the new date in a text box. eg 05/08/07 + 5 should have a new date of 12/08/07.

thanks

dee

 

[burrcm]

In a text box on a form set the control source to = Date()+5

Chris B

 

[deepatel70]

but i want it to exclude the Saturday and Sunday??

 

[burrcm]

Hmm. Best to ask the question to which you want the answer at the beginning. So, if the result of adding 5 days will be Saturday or Sunday, what do you want it to display?

Chris B

 

[deepatel70]

ok, so for example if the date is 05/08/07 and we add 5 days then the new date should be 12/08/07

 

[burrcm]

Maybe this =IIf(Weekday(Date()+5)=1 Or Weekday(Date()+5)=7,"Shut today",Date()+5)

Chris B

 

[burrcm]

OK, you want it to be the next Monday, hang on a minute - have to do Sat and Sun separately.

Chris B

 

[burrcm]

=IIf(Weekday(Date())=3,Date()+6,IIf(Weekday(Date())=2,Date()+7,Date()+5))

Should do it.

Chris B

 

[deepatel70]

that didn't work mate, the result showed as 10/09/2007

 

[burrcm]

Which is Monday is it not?

Chris B

 

[deepatel70]

Ok, if the 05/09/07 is today(wednesday) and if i add 5 working days then the date should be 12/09/07 (wednesday)

thanks

 

[burrcm]

So what you really want is a date 1 week ahead of the entered date? So it really doesn't have anything to do with weekends. Going to work now, will post that solution tonight unless someone beats me to it.

Chris B

 

[burrcm]

Am I still missing something I wonder? Date()+7 adds a week, eg Monday +7 is next Monday. Will this be used at weekends which means a different outcome is required. I don't understand

Chris B

 

[deepatel70]

basically what i've done so far is, i've worked out the date difference between to dates which give me x working days, now i want to add the x working days to my end date but it can't include weekend as the business is closed over the weekend.

hope this helps

dee

 

[burrcm]

OK. If you wrap your whole calculation in an IF as I did in comment #8, you can push it to Monday if the result would otherwise be Sat or Sun. If you can't get it to work, post the whole calculation so I can see exactly what you are up to.

Chris B

 

[deepatel70]

i have got this:
Private Sub Command7_Click()

'Enddate is a textbox
'Numofdays is a textbox
'Newdat is a textbox(displays results)

Dim intCount As Integer
Dim Myenddate As Date
Dim Tempdate As Date


intCount = 0
EndDate.SetFocus
Myenddate = EndDate.Text
NumOfDays.SetFocus
Tempdate = EndDate.Value

Do Until intCount = 3 'this is the number of days to be added
If Weekday(Myenddate) = "1" Or Weekday(Myenddate) = "7" Then
intCount = intCount
Else
intCount = intCount + 1
Tempdate = Tempdate + 1
End If
Loop

NewDate.Value = Tempdate

End Sub

but it doesn't work.

Dee

 

[deepatel70]

Private Sub Command7_Click()

'Enddate is a textbox
'Numofdays is a textbox
'Newdat is a textbox(displays results)

Dim intCount As Integer
Dim Myenddate As Date
Dim Tempdate As Date


intCount = 0
EndDate.SetFocus
Myenddate = EndDate.Text
NumOfDays.SetFocus
Tempdate = EndDate.Value

Do Until intCount = 3 'this is the number of days to be added
If Weekday(Myenddate) = "1" Or Weekday(Myenddate) = "7" Then
intCount = intCount
Else
intCount = intCount + 1
Tempdate = Tempdate + 1
End If
Loop

NewDate.Value = Tempdate

End Sub

This is what i've done, but doesn't work.

 

[Tanner65]

I'm a little confused, help me out a little bit.

If you get an order on Monday, when is it supposed to leave? The following Monday or Friday?

 

[Tanner65]

Nevermind, I've looked at your code and realized what ya were trying to do.

try this:

Dim intCount As Integer
Dim Myenddate As Date
Dim Tempdate As Date


intCount = 0
enddate.SetFocus
Myenddate = enddate.Text
numofdays.SetFocus
Tempdate = enddate.Value

Do Until intCount = numofdays.Text 'this is the number of days to be added
If Weekday(Tempdate) = "1" Xor Weekday(Tempdate) = "7" Then
intCount = intCount
Tempdate = Tempdate + 1
Else
intCount = intCount + 1
Tempdate = Tempdate + 1
End If
Loop
newdate.Value = Tempdate

A little explaination of what I changed:
Firstly, the intcount is static, yet you have a textbox to control it, so I set the text value as the intcount.
Secondly, ya needed to use the Tempdate as your if then statement to check against what day of the week it is, not the end date.
Thirdly, you need to add a day to the order each time you have a weekend day so that you wouldn't be stuck in an infinite loop and screw big things up.

 

[deepatel70]

thanks for that

dee