Hi,
I've written a code to do what I understand from your words. Tell me if it
does what you want. In order to run that code you need a form with 2 textbox
controls: the first textbox is called 'txtSeedDate' and the second textbox
is called 'txtTargetDate'. In addition, you need to have a command button
called 'cmdCalculate' on that form.
After you set the dates into the 2 textboxes, you should click the
cmdCalculate command button, and see the result according to the dates
entered. For example of you enter '28/02/2006' as the seed date and
'06/03/2006' as the target date, you'll get the following result:
Day #1 of platoon D
Night #1 of platoon C
After you understand the logic, you might change the code to suit your
needs.
As I posted in my previous reply, there might be a chance that I havn't
understood you perfectly, so you might clarify your demands or just fix the
code by yourself.
In order for the code to work, create the form with the 2 textboxes and
command button I've mentioned, then copy the following text into the form's
code:
'*********************Code Begin*****************************
Option Explicit
Private Sub cmdCalculate_Click()
'Variables declaration
Dim intDifferenceInDays As Integer
Dim intDaysInOneCycle As Integer
Dim intDifferenceInDaysOfTheCycle As Integer
Dim intTotalNumberOfPlatoons As Integer
Dim intDaysBetweenPlatoonSwitches As Integer
'Variables value settings (You might change values)
intDaysInOneCycle = 10
intTotalNumberOfPlatoons = 5
'Following line means that each platoon is working
'2 days then 2 nights, so that each platoon
'is replaced cylicaly every 2 days.
intDaysBetweenPlatoonSwitches = 2
'Input Validation
If IsNull(Me.txtSeedDate) Or IsNull(Me.txtTargetDate) Then
MsgBox "Missing date"
Exit Sub
End If
'Calculate difference in days between 2 dates entered
'"d" is used in order for the difference
'to be calculated in units of days
intDifferenceInDays = DateDiff("d", _
Me.txtSeedDate, Me.txtTargetDate)
'Calculates the difference in days according
'to the cycle. e.g. the difference between
'1/1/2006 and 13/1/2006 is 2 since cycle is 10 days
intDifferenceInDaysOfTheCycle = _
(intDifferenceInDays Mod intDaysInOneCycle)
'Show who works on the target day
MsgBox "Day " & _
DayPlatoonAccordingToDifference(intDifferenceInDaysOfTheCycle, _
intDaysInOneCycle) & _
vbCrLf & _
"Night " & _
NightPlatoonAccordingToDifference(intDifferenceInDaysOfTheCycle, _
intDaysInOneCycle)
End Sub
'************************************************************
'Function Name: DayPlatoonAccordingToDifference
'Purpose: Returns the letter (a, b, etc.) of the platoon
' who works on the day which is in a given
' difference from the seed day.
'Example: 0 and 1 will give: "A", 2 and 3 will give "B" etc.
'Input variables:
' intDifferenceInDaysOfCycle - The difference in "cyclic"
' days between the seed date and the target date
' intDaysInOneCycle - Amount of days in one cycle
'************************************************************
Function DayPlatoonAccordingToDifference(intDifferenceInDaysOfCycle _
As Integer, intDaysInOneCycle As Integer) As String
Dim intACharASCIIValue As Integer
Dim intNormalizedDifferenceInDays As Integer
intACharASCIIValue = 65
intNormalizedDifferenceInDays = _
NormalizedDifferenceInDays(intDifferenceInDaysOfCycle, _
intDaysInOneCycle)
Select Case (intNormalizedDifferenceInDays Mod 2)
Case 0
DayPlatoonAccordingToDifference = _
"#1 of platoon "
Case 1
DayPlatoonAccordingToDifference = _
"#2 of platoon "
End Select
DayPlatoonAccordingToDifference = _
DayPlatoonAccordingToDifference & _
Chr(intACharASCIIValue _
+ ((intNormalizedDifferenceInDays - _
(intNormalizedDifferenceInDays Mod 2)) / 2))
End Function
'************************************************************
'Function Name: NightPlatoonAccordingToDifference
'Purpose: Returns the letter (a, b, etc.) of the platoon
' who works on the night which is in a given
' difference from the seed day.
'Example: 0 and 1 will give: "E", 2 and 3 will give "A" etc.
'Input variables:
' intDifferenceInDaysOfCycle - The difference in "cyclic"
' days between the seed date and the target date
' intDaysInOneCycle - Amount of days in one cycle
'************************************************************
Function NightPlatoonAccordingToDifference(intDifferenceInDaysOfCycle _
As Integer, intDaysInOneCycle As Integer) As String
NightPlatoonAccordingToDifference = _
DayPlatoonAccordingToDifference(intDifferenceInDaysOfCycle _
- 2, intDaysInOneCycle)
End Function
'************************************************************
'Function Name: NormalizedDifferenceInDays
'Purpose: For positive input values returns original number.
' For negative input returns original number+cycle.
'Example: +5 remains +5. -2 becomes +8.
'Input variables:
' intOriginalDifference - The difference in "cyclic"
' days between the seed date and the target date
' intDaysInOneCycle - Amount of days in one cycle
'************************************************************
Function NormalizedDifferenceInDays(intOriginalDifference _
As Integer, intDaysInOneCycle As Integer) As Integer
If intOriginalDifference < 0 Then
NormalizedDifferenceInDays = intOriginalDifference _
+ intDaysInOneCycle
Else
NormalizedDifferenceInDays = intOriginalDifference
End If
End Function
'*********************Code End*******************************
There also might be clever ways of doing what I've written. It's 06:30 in
the morning where I live...
Hope it helps.
Regards,
Amir.
