Date Ranges

[Slobey]

Hi everyone,

I am making a simple time sheet solution and I an trying to make a field display a Date or "Vacation" or "Sick".

I have separate fields for each day of the week. For example I have "IN_MON", "OUT_MON" and "HRS_MON". I have 2 text fields called "DATES_VAC and "DATES_SICK".Right now "HRS_MON" looks like this:

Case(

PatternCount (DATES_VACA ;Date_MON );

"Vacation";

PatternCount (DATES_ SICK ;Date_MON );

"Sick";

If(OUT_MON - IN_MON>8;

"8";

OUT_MON-IN_MON

)

)

What I want to be able to do is let the user enter single dates as well as date ranges. For example if a user enters the following in the "VACATION" field:

12/20/04, 12/23/04, 01/01/05 - 01/06/05

As I have it now, the field will recognize the individual dates. but I want the "HRS_MON to be able to see that monday 02/03/05 is in the range of 01/01/05 - 01/06/05 and enter "VACATION".

Am I making any sense here?

I would also like to be able to allow the user to enter different formats in the "VACATION" field like

01/01/05

01/01/2005

Jan 1 2005

January 1, 2005

and still recognize the date. but I will settle for the date ranges for now.

If anyone can help I would be forever in your debt.

Tnanks

[Ender]

You can build (or find) a custom function to do this. However, I think this design will cause you problems down the road. You'll be able to do much more granular reporting if you put your time entries in a related table, where each record is one time range. You could have the parent record as one payperiod, with the related entries as hours per day (or even allow multiple entries per day, if you wanted.) But I'd recommend not allowing date ranges for an entry.

[transpower]

I agree with Ender, here. Have a separate table, vacation, with three fields: start_date, end_date, employeeID. If it's just one day, enter the same date for both. Use a standard date format (like mm/dd/yyyy) to avoid confusion. Have another table, sick, with the same three fields. Or use just one table, with an added field for type (vacation or sick).

[Ender]

Sorry, I was not suggesting separating vacation and sick time into separate tables, but instead put ALL time entries into one related line items table.

Since Slobey seems to be tracking Start Time and End Time for each day, it makes sense to put vacation and sick days as a type of Activity for each day. The Activity Type for regular work time would be "Work" or "Regular" or something, then "Sick" and "Vacation" are the other types. If you want to be able to track multiple types of activities for each day (like working for 6 hours, sick for 2 hours,) then allow multiple activity lines for each date.

So you might have a week that looks like this:

Mon 8AM - 4PM Regular

Tue 9AM - 3PM Regular

Wed 9AM - 5PM Vacation

Thu 9AM - 5PM Vacation

Fri 9AM - 5PM Sick

This means data entry may look different from what Slobey was imagining, but I think it's clearer from the user's perspective as to how to fill it out. Each day will have some sort of entry.

[Slobey]

Thanks guys,

your ideas were a great help, I appreciate it

[Barbecue]

Slobey, if you really want users to be able to enter multiple dates and date ranges as you describe, you could still do this with Ender's approach. You just need to make a distinction between how the user enters the information and how it is stored in the database.

You could create a global text field for the user to enter their date ranges in, and then using a script to parse the text into a list of individual dates, and then generate records based on the list. You would definitely want to pick a single standardized way of entering the date information though, as allowing for users to enter a variety of different formats would make the parsing much more complex.