Can someone please help. When I use the calculation for days of the week, I get an accurate count but if the start and end dates are the same, the calculation reads a ? rather than zero days. Any suggestions?

Can someone please help. When I use the calculation for days of the week, I get an accurate count but if the start and end dates are the same, the calculation reads a ? rather than zero days. Any suggestions?

- 1 person found this helpful
DayOfYear ( 5 * Int ( ( Admission date - Date of Screening ) / 7 ) +Middle ( "0012345501234544012343340123223401211234010012340" ; 7 * (DayOfWeek ( Date of Screening) - 1 ) + DayOfWeek (Admission date ) ; 1 ))

i got this calculation off of another discussion - it works except for the same date for start and stop.

If you use:

FileKraft wrote:

GetASNumber ( DateField2 - DateField1 )

And both dates are the same it would calculate internal to:

Date ( 1; 0 ; 1 )

Which is an illegal date for FileMaker. So if you need the calendar-days you should use:

GetAsNumber ( DateField2 ) - GetAsNumber ( DateField1 )

With this formula it is even possible to work with dates like:

DateField1 > DateField2

Better check that again.

GetASNumber ( DateField2 - DateField1 )

will evaluate as:

GetAsNumber ( 0 )

which is:

0

If the dates in the two date fields are the same.

On the other hand:

DayOfYear ( 0 )

produces an evaluation error and that's why this calculation returns a ? if the dates are the same. Thus, adding if or Case to check for both dates the same is a good idea.

IF that's what you want.

It turns out that it's not what the OP wants here, hence the more complex calculation. It turns out that the OP wants the number of Weekdays between dates, not the number of days between dates. this is all spelled out in the discussion if you read all the replies.

My last reply here was to correct an error.

There's no evalutation error in GetasNumber ( Date2 - Date1 ) should date2 and date1 be the same. There is no need for GetAsNumber so that can be removed. The original expression returns a ? because the WeekOfYear function evaluates as WeekOfYear ( 0 ) when the dates are the same.

My apologize for someone made the "49numbers". I thought it should be symmetry for each dow, but I was incorrect.

And I thought it should count "monday to saturday" as same as "monday to friday" since saturday is excluded, but the calculation (and OP) doesn't want it. It seems my need was always count between same days as 1... Mine is number of days as count of date, OP's calculation is number of days as interval, or count of "end of date".

I've used that working days calculation a few times in my solutions, but never really understood how someone came up with it. It seems like there should be a simpler formula, but it always seems to work for me.

It can be found here: Calculating Number of Weekdays (Work Days) Between Dates | FileMaker from FMI's Knowledge Base.

It's a bit amazing there isn't an internal function for weekdays' calculation. Possibly taking an argument for what you mean by "weekdays". I'm really surprised that FMP's date functions are so weak. The included date functions leave out lots of necessary functionality so you end up doing lots of work you shouldn't have to do, IMHO. Dates are so key in databases and, well, uhh, I'll leave it at that.

If the function you pointed to works in every case (a true "black box"), I wouldn't worry about the code. A dozen different programmers will come up with a dozen different ways to solve the same problem. That's kind of cool.

i am not convinced this should be built-in FMP. the function is simple if you give it some thought. I'd rather have FMInc. give an improved version in other areas and extend the specs which haven't much changed since FMP7 for example recursion limit of max10k (50k) calls or length of script-parameters to be exchanged etc ..

If these limits would be removed and the FMP would scale nicely then many more home-made functions could be possible.

- 1 person found this helpful
I have my own weekdays and workdays functions (outside FMP) so I know what you mean.

However, even Excel, which FMI uses for so many comparisons has a WEEKDAY function.

WEEKDAY function - Office Support

In the end, abstraction is your friend. You shouldn't have to constantly "think about" (and implement) common and simple things. They should be, well, abstracted.

By you creating your own WEEKDAYS function, as an example, you then need to maintain it (if necessary, which means you then need to understand it), transfer it to other apps, etc.. IOW, the entire configuration load is now on you.

---

However, I completely agree with the rest of your posting. I have documented over 52 sorely missing features in FMP (like

search/replace in scripts, a version 1.0 feature). Being able to debug CFs seems like an obviously missing feature. Making CFs more like, well,**BASIC***functions*, and less like, well,*calculations*would be another thing...Thanks for your posting!

thank you adding this to the calculation worked like a charm