Calculate absences within a date range, separated by months.

[Melborne]

Hi!

 

I have an Hours Sheets table, and hour sheets are created per month.

I have Absences table which has absences specified by start and end date.

and I have a Calendar days table, which is related with Absences with start date and end date.

 

Now,

When I create an absence spanning over two or more months, (e.g 26th March to 5th April)

I want to get related calendar days for the particular month in hour sheet. (i.e if I create an absence from  26th March to 5th April I want to have 3 days (26th, 27th and 28th) in hours sheet of February month and 5 days (1st, 2nd, 3rd, 4th and 5th) in hours sheet of March.

 

I am not able to find a way to separate out the days based on a month. ( I can not use portal filter, since I need the days to use in calculations)

 

I have attached a sample file.

 

Any advice/suggestions will be greatly appreciated.

 

Thanks!

absences.fmp12.zip

[GisMo]

I looked over your sample file and I think understand what you're trying filter..But I think you may not be be approaching this in the best way with regards to tables. Can you shed a little more light on what you're doing? Do you only have one record for one calendar day? Or are there multiple records for each calendar day? Any other information would be helpful too.

[Melborne]

@GisMo Thank you for the reply.

 

The days(Calendar days table) are created for entire year, only 1 record per day.(~365 per year pre-generated records)

Absence table is where I create absences with a start and an end date. It could span over multiple months.

I have a table for Hours sheet (hours sheets table) where an hour sheet is created per month.

I want to grab number of days in Absences per hour sheet i.e per month.

 

Example:

If user applies for Absence from 26th Feb to 5th March:

 

I want 26th Feb to 28th Feb(or whatever the end date of Feb) in hour sheet of February and 1st to 5th in hour sheet of March.

[GisMo]

Sending you a PM