From Dwayne Wright PMP, PMI-ACP, CSM
Certified FileMaker Developer
A READER ASKS: I'm a fan of your blog. I was looking for suggestions on how to write a formula that would return the following. It seems common enough however I'm having trouble finding an example. I'd like to have a field that calculates the weeks/days remaining until a due date and returns that number as a decimal. This layout would show the weeks remaining until a target deadline is due, each day of the week would be valued at .2, a whole week [5 working days] would be represented as 1.0. Therefore a week and two days is represented as 1.4, a weeks and four days 1.8 weeks etc. This would be calculated using the current date. Any thoughts on how to write this?
DWAYNE RESPONDS: The actual number of days and weeks is not that hard to do using Date functions. A bit of a twist is the working days calculations and for something like this, I try to ride on the shoulders of the calculation giants. To do this, you will likely need (if you don't already have) a copy of FileMaker Advanced to take advantage of its ability to add custom functions. Then check out all the date related functions you can find at sources like the custom function library at briandunning.com and http://www.fmfunctions.com/ .
I'll go ahead and post this to my Calculations Explored Blog and if anyone has any comments about this, I'll definitely pass them on to you.
More info about the author and FileMaker in general, contact me at firstname.lastname@example.org.
© 2008 - Dwayne Wright - dwaynewright.com
The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.