Jump to content

Calculate absences within a date range, separated by months.


Melborne
 Share

This topic is 2804 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

@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.

Link to comment
Share on other sites

This topic is 2804 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.