Convert day, month, year fields to a date

[DoctorJDM]

Hi

Form has separate fields for

- day, eg value = 2
- month, eg value = 12
- year, eg value = 2006

How can they be put together to get a true date in format say 2-Dec-2006?

 

[Remou]

You need DateSerial

[tt]DateSerial(2006,12,2)[/tt]

 

[TheAceMan1]

How are ya DoctorJDM . . .

Curious . . . why the seperate date fields?

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[DoctorJDM]

Thanks Remou. Looks good, will have a go.

AceMan - bit embarrassed about this but all I want to do is work out a person's holiday allowance if they join part way through the current holday year.

So, with a full allowance of say 20 days and the year going from 1 April to 31 March in the following year, someone joining in July will have about 15 days, someone joining in February will have about 2 days.

I can identify the year end but then needed the full date with 31 March to be able to do the calculation.

If there's an easy way I'd be delighted to hear it.

Thanks both for the time you keep devoting to beginners like me!

 

[DoctorJDM]

Gulp, have concocted this as the way of calculating the number of days holiday owing to a person joining a company part way through the year. It works but seems horribly complicated!

=IIf(DateSerial(IIf(DatePart("m",[JoiningDate])<4,DatePart("yyyy",[JoiningDate]),DatePart("yyyy",[JoiningDate])+1),3,31)-[JoiningDate]<365,Round((DateSerial(IIf(DatePart("m",[JoiningDate])<4,DatePart("yyyy",[JoiningDate]),DatePart("yyyy",[JoiningDate])+1),3,31)-[JoiningDate])*[DaysAllowance]/365+0.01),[DaysAllowance])

 

[DoctorJDM]

Even bigger gulp, it needs a check that the calculated holiday year end is after today's date, hence this.

=IIF(Date()>DateSerial(IIf(DatePart("m",[JoiningDate])<4,DatePart("yyyy",[JoiningDate]),DatePart("yyyy",[JoiningDate])+1),3,31),HolidayAllowance,IIf(DateSerial(IIf(DatePart("m",[JoiningDate])<4,DatePart("yyyy",[JoiningDate]),DatePart("yyyy",[JoiningDate])+1),3,31)-[JoiningDate]<365,Round((DateSerial(IIf(DatePart("m",[JoiningDate])<4,DatePart("yyyy",[JoiningDate]),DatePart("yyyy",[JoiningDate])+1),3,31)-[JoiningDate])*[DaysAllowance]/365+0.01),[DaysAllowance])

Post Script; there must be an easier way of calculating how much of the HolidayAllowance is due to a person coming to a company on JoiningDate!

 

[Hans8823]

Public Function Allowance(ByVal JoiningDate As Date) As Double
    If Month(JoiningDate) < 4 Then
        Allowance = DateDiff("d", JoiningDate, DateSerial(Year(JoiningDate), 4, 1)) / 365 * 20
    Else
        Allowance = DateDiff("d", JoiningDate, DateSerial(Year(JoiningDate) + 1, 4, 1)) / 365 * 20
    End If
End Function

 

[TheAceMan1]

DoctorJDM . . .

20 days per year as you've prescribed works out to be [BLUE]1day 16hrs[/blue] of accurual per month!

What matters is how your handling this . . . do they accrue as per above per month, or are the current days available from that point upon joining the company (as if I didn't know!)?

See Ya! . . . . . .

Be sure to see FAQ219-2884: