9 Replies Latest reply on Jun 5, 2012 1:00 AM by Jenny_1

# How to calculate an end date after adding missed days in a full month?

### Title

How to calculate an end date after adding missed days in a full month?

### Post

Hi All

I want to set a calculation to add missed lesson days to an course completion end date, in a 7 day week.

Using FP11 (on Mac) - I have set a field for a course completion date (a date field) plus a field to enter the number of days of the course missed (a number field) & I would like the calculation to automatically add the missed days to the completion date.

I have not been able to find any hints on how to do this, can you help?

Jenny

• ###### 1. Re: How to calculate an end date after adding missed days in a full month?

I would create an additional field.

The fields I think you need are:

CourseCompletionDate  -  A Date Field
MissedDays  -  A number field
c_CourseCompletionDateCalculated  -  A calculation field with the calculation result set to "date"

The calculation is as follows:

If ( IsEmpty ( MissedDays ) ; CourseCompletionDate ; CourseCompletionDate + MissedDays )

This IF statement says: If there is no date in the field "MissedDays" in other words if there are no days missed, the completion date just remains the same as the one that was entered.
If there is a value in the "MissedDays" field, in other words, if someone missed a few days, the calculation adds those missed days to the Completion Date.

That way you have one field to set the completion date but if there are any missed days they get added to the end date.

• ###### 2. Re: How to calculate an end date after adding missed days in a full month?

That can be simplified to just:

CourseCompletionDate + MissedDays

There's no real need for the If funciton here since an empty missedDays field will evaluate as zero.

• ###### 3. Re: How to calculate an end date after adding missed days in a full month?

Hi DaSaint & Phil

I used your calculation Phil which worked the only problem is the Corrected Completion Date  is the number if MissedDays added to 01 Jan 0001 so for 6 Missed Days I have 06 Jan 0001 & not the actual CompletionDate?  I don't know if it has any affect but I did confirm teh computer is set at the correct current date & time!

Can you tell me how to correct this?

Jenny.

• ###### 4. Re: How to calculate an end date after adding missed days in a full month?

I can't seem to be able to replicate that behavior.

What I have is:

A number field called "MissedDays"
A date Field called CompletionDate
A calculation field (c_CalculatedCompletionDate) set to result in a date with the calculation:

CompletionDate + MissedDays

The calculation field is not a number field set to an auto enter calculation, but an actual calculation date.

If I give in 01/01/0001 and I put 6 in the MissedDays field I get a calculated date of 07/01/0001

• ###### 5. Re: How to calculate an end date after adding missed days in a full month?

Completion date must be a field of type date when you check its type in Manage | database | fields.

It sounds like you have a text feld for your completion date--which is not a good way to record dates.

• ###### 6. Re: How to calculate an end date after adding missed days in a full month?

Terrific.

Thanks to Phil & DaSaint, I did have the completion date as a date field but the corrected completion date, I had as a date field & then a calculation field instead of a calculation field showing the result as a date.

All is working well thank you.

Jenny.

• ###### 7. Re: How to calculate an end date after adding missed days in a full month?

Phil & DaSaint

Also, could the calculation - CompletionDate + MissedDays; be adjusted for weeks missed?  That is if we are looking at weekly classes & wished to keep track of missed weekly classes by adjusting the completion date with the additional missed weeks added.

I did try substituting with any luck?

Thankyou.

Jenny

• ###### 8. Re: How to calculate an end date after adding missed days in a full month?

To calculate with weeks you would use the following fields:

CompletionDate  -  A Date field
WeeksMissed  -  A number field
c_NewCompletionDate  -  A Calculation field

The calculation would be: CompletionDate + ( WeeksMissed * 7)

And the calculation result would be set to "Date"

If you want to have the ability to fill in both days and weeks you would do the following:

CompletionDate  -  A Date field
DaysMissed  -  a number field
WeeksMissed  -  A number field
c_NewCompletionDate  -  A Calculation field

The calculation would be: CompletionDate + DaysMissed + ( WeeksMissed * 7 )

Then you can fill out both days and weeks. For instance: missed 2 weeks and 3 days.

• ###### 9. Re: How to calculate an end date after adding missed days in a full month?

Thank you all,

I now have it all working correctly.

Thanks again for yout time.

Jenny