5 Replies Latest reply on Jul 7, 2010 1:55 PM by philmodjunk

Date Calculation Help Needed

Title

Date Calculation Help Needed

Post

FMP 10 Advance

Mac 10.5

Novice

I have the following fields:

Date IN

Date OUT

Hours worked = (Date OUT – Date IN)*10

I am trying to determine a calculation that would return the date when the field "Hours worked" = 3600.  Any thoughts?

My actual problem is more complicated than this, but I wanted to start off easy.

Thank you in advance.

• 1. Re: Date Calculation Help Needed

Are Date In and Date OUT time stamp fields? These fields measure time to the nearest second so Date OUT - Date IN will return the difference in time in seconds and (Date Out - Date IN) * 10 = 3600 only when there is a difference between the two fields of exactly 360 seconds (1 hour).

The "date" you want returned, would that the Date IN or the Date OUT? Theoretically you could have values such as 11:59:00 July 4th for Time IN and 12:59:00 July 5th and get the difference times 10 that you specify.

• 2. Re: Date Calculation Help Needed

PhilModJunk,

I appreciate your response and apologize for being too vague.

The dates entered in the Date IN and Date OUT fields are from the FMP drop down calendar and I hope they are in the MM/DD/YYYY format.  Each employee will invoice 10 hours of work for each day of the date range (Date OUT - Date IN).

Is it possible for FMP to calculate the date (MM/DD/YYYY) when an employee will reach 3600 of hours worked between that date range?

Thank you for your time.

• 3. Re: Date Calculation Help Needed

If ( ( Date OUT - Date IN ) > 360 ; Date OUT ; "" )

Set this calculation to return date. Depending on how you use this calculation, you may be able to get away with = instead of >.

Note that this assumes that employees are working 7 days a week and that they don't take any holidays.

• 4. Re: Date Calculation Help Needed

PhilModJunk,

Thank you for pointing me in the right direction.  Your calc does provide the date however only if the Date OUT equals the requirements.  I'm sure that I didn't specify that a employee's period of work could span two years before obtaining the require hours for his "bonus".

Example:  Employee works between 1/1/2010 and 5/1/2011 at 10 hours / day.  On what date will he have reached 3600 hours?

Thanks for your calc,  I'll tool around with it and try and come up with something.

Cheers!

• 5. Re: Date Calculation Help Needed

Only if the date out equals or exceeds the requirements. That's the best I can suggest without knowing more about your database. The structure has appeared odd to me from the start, but that could be due to not having the full picture here.