Total of DateEnd-DateStart.

[edudna]

Dear experts,

I have to put a problem again although I will try to give solutions of my own. I make this because I always seek for my huge I confess educational database to run smoothly and without problems.

THE PROBLEM.

Let us supppose that a teacher taught to five different schools from a DateStart to a DateEnd per year in last five years. We want to calculate the total number of years, months and days that the teacher taught to the schools in the last five years. But also we want the total number are not only in days but have the format xyears, vmonths, zdays. I hope you understand if you do not I will give an example but I think it is relatively easy to comprehend.

Pascal

One I know that I do not know almost anything.

Socrates

[mr_vodka]

Whats going on here... Everybody is talking about (Years, Months, Day) today. haha.

Anyway, Pascal, this is a recent thread on almost the same subject. Follow Comment's link as well to finish the read.

Years, Month, Day

[edudna]

Does not help me too much this thread because my calc is like:

Let([worktime=DateEnd-DateStart];sum(worktime)) but gives only the first year not the sum of the others.

[Søren Dyhr]

But also we want the total number are not only in days but have the format xyears, vmonths, zdays

Have you thought about how flimsy this result is if some teacher only educates februarys each year, or works in a timespan including a leap-year. Summing months makes only sense inside one single year - but is still uttely dependant on the start of the timespan.

--sd

[edudna]

It is not easy as I see. I have to exclude it finally from the database but it was a great utility to calculate the whole employment time of the teachers. But for the issue you put Soren I have to say that if he or she was employed for example only 15 days in February we have the format:

0 years 0 months 15 days it really does not matter if it is in this format. But how we calculate the total of time employement from the dates?

Pascal

One I know that I do not know almost anything...

LOL

[Søren Dyhr]

I can hear that you not quite get it, if you simply takes the difference of days, will it always be with offset at 1st of january year 0000, which would be misleading if you're working from 1st of feb. to 30th of april ...you would right aways say it's 3 month, but if you take the timediffernce will it be calculated as 2 month and 28/29 days.

So it's actually quite a calc' to establish it for each year, and when it comes to it, is it like comparing apples with oranges - an month is not just a month!

--sd

[edudna]

If we finally can do the calculation and we have

two records for a teacher like the follow:

DateEnd1-DateStart1= x1 years, u1 months, z1 days

DateEnd2-DateStart2= x2 years, u2 months, z2 days

How can we calculate x1+x2 years, u1+u2 months, z1+z2 days without months>12 and days>30?

[edudna]

I think that this topic should be answered and I put it with more specific sentence here:

If we have a field in records contains time in the format:

xyears,ymonths,zdays how can we get the summary of all the fields contain time in the records in the same format?

Example

Time1: 5years, 6months, 23 days

Time2: 4years, 4months, 25 days

How can we calculate the total of time1+time2 and a possible time(x) to give us the result 9years, 11 months, 17days

Pascal

[LaRetta]

Let me see if I understand your request ...

This non-time time field, which was created by turning a perfectly nice date range into inaccurate results as text now needs to be pulled back apart so you can summarize it by each piece (year, month, day) through many records, so you can then AGAIN combine the results back into an inaccurate text field?

Why not just take the original date range, calculate the total days then apply your non-time time calculation to it instead? It would be more accurate (although still off). Each time you combine it then split it apart then combine it again, it will go days off the accuracy. Does Humpty Dumpty come to mind?

I think that this topic should be answered...

You have already asked the question. A new thread was not appropriate. You could post back on the thread asking for more assistance or wording your question differently. You KNOW how we all feel about duplicate threads. I could have just deleted this but well, I was so dumbfounded by your request (and lack of respect for Forum rules, which you know) that I forgot. Please do not do it again.

LaRetta

Edited January 27, 2007 by Guest

[edudna]

This happens because really sometimes I do not understand even myself...

Look please what I have now and help me accordingly because I have a terrible headache with this:

If we have two fields

StartDate and EndDate

The calc EndDate-StarDate that give us in xyears, ymonths, zdays the time of employment is the follow:

If(GetAsNumber(StartDate)="";"";GetAsText(Year(EndDate) - Year(StartDate) - If(GetAsNumber(EndDate) < GetAsNumber(Date(Month(StartDate); Day(StartDate); Year(EndDate))); 1; 0)) & " Years, " & GetAsText(Mod(Month(EndDate) - Month(StartDate) + 12 - If(Day(EndDate) < Day(StartDate); 1; 0); 12)) & " Months, " & GetAsText(Day(EndDate) - Day(StartDate) + If(Day(EndDate) ≥ Day(StartDate); 0; If(Day(EndDate - Day(EndDate)) < Day(StartDate); Day(StartDate); Day(EndDate - Day(EndDate))))) & " Days")

HOW CAN WE ADJUST THE ABOVE CALCULATION TO GIVE US THE TOTAL OF TIME IN THE SAME FORMAT FOR ALL THE PERIODS THAT A TEACHER WORKED?

I think that now the thread is not the same of course.

Pascal

[comment]

On the first day, I walked for 3 hours and 700 yards.

On the second day, I walked for 2 hours and 500 yards.

What is the total distance (in hours and yards) that I have travelled?

And no, my speed is NOT constant.

[Søren Dyhr]

...checkout this:

http://www.briandunning.com/cf/518

Then split them appart in a repeating field for the summary:

http://edoshin.skeletonkey.com/2006/12/crosstab_report.html#more

But what if the sum exeedes 12 month say 14 month...??

--sd

[edudna]

Dear Soren or anyone expert,

I will be gratefull if you have time to explain a little more how to split years months and days and calculate the sum of them. Split function seems grate but unfortunately I do not have any idea of it as a novice.

Pascal

[Søren Dyhr]

But as such, doesn't the reasoning behind make much sence anyhow? You're mixing apples and oranges ...the figures are roughly giving a hint, even getting a age of a person is slightly iffy.

If(not IsEmpty(birthday);

int((Get ( CurrentDate ) - GetAsDate ( birthday))/365.25);"")

...such as suggested by Bailey Kessing in Dunnings page.

It's only a rough average only dealing slightly correct when people are within their best business years (if such exists??)

Re-read LaRettas post again!!!

--sd

eduna.zip

[LaRetta]

I will be gratefull if you have time to explain a little more how to split years months and days and calculate the sum of them. Split function seems grate but unfortunately I do not have any idea of it as a novice.

Pascal, you continue to repeat your same request. I have combined your threads for you (no need to thank me). It is amazing that, when provided with information, you do not STUDY it.

[color:blue]STUDY

v.tr.

1. To apply one's mind purposefully to the acquisition of knowledge or understanding of (a subject).

2. To read carefully.

3. To memorize.

4. To take (a course) at a school.

5. To inquire into; investigate.

6. To examine closely; scrutinize.

7. To give careful thought to; contemplate: study the next move.

You can not tell me that, within the two-hour period from when Soren provided you with links to Mikhail's crosstabs and the CF on Brian's site and the time you answered him AGAIN repeating the same question, that you studied what was provided? You did not respond with questions about the techniques suggested nor did you indicate that you even TRIED anything. Have you even READ how FileMaker makes summaries!? Have you even read Help about Sum()?

Do you know what the word 'homework' means? If you are not willing to do the work then it is probably time you hire a real developer.

LaRetta

[edudna]

I not only study but I have a terrible headache sometimes of the problems. Do not blame me. You are not obliged to answer but if you do it I always thank you and I do not forget to wish you the best. However I am not going to humiliate myself in this forum. I am an educator not a programmer and all I do is to help education in my small country.

When I put a question to you I leave it for you in order to not loose time and I am looking in another direction. Todays morning was really creative to me. I found this:

We suppose that we get the summary of fields that contains the calc: DateEnd-DateStart.The summary give us a total number of days in field SummaryOfTime.

Let ([

y= Floor(SummaryOfTime/ 365);

m= Floor(Mod(SummaryOfTime; 365)/30);

d=Floor(SummaryOfTime-(y * 365) -( m * 30))];

y & " years, " & m & "months, " & d & "days")

It is very easy indeed.

I think that the topic is ending about here.

Pascal

[Lee Smith]

Hi Pascal,

...Snip .. I am an educator not a programmer and all I do is to help education in my small country.

Actually, a person could be both. However, FileMaker is not a Programing language, and you will find that the members come from a variety of backgrounds, and possess skill, from just open the box, to the crème de la crème of the FileMaker world.

Our members share a common goal, and that is to learn skills and knowledge that then don't currently possess, and/or to share their knowledge with other. Hmm, did I just describe an academic environment there?

You have received replies from some of our best, however, your replies didn't bare out that you had been reading, or trying any of the suggested answers.

I'm wondering how much patients you would have from a student that you had supplied the answers to, but kept asking the same question over and over again?

When I put a question to you I leave it for you in order to not loose time and I am looking in another direction. Todays morning was really creative to me. I found this:

Anyway, I'm glad you finally found something will worked for you.

Lee

[LaRetta]

Why not just take the original date range, calculate the total days then apply your non-time time calculation to it instead?

I understand that you are not a programmer. Many who post questions on this Forum are not programmers and they receive help every day. Nor has anyone asked you to humiliate yourself. But we do expect our suggestions to be read, considered, and tried and if you get stuck or don't understand then ask for clarification. Where did you ask me for clarification on this approach? I would have been happy to provide an answer.

Instead, your response yelling was, "HOW CAN WE ADJUST THE ABOVE CALCULATION TO GIVE US THE TOTAL OF TIME IN THE SAME FORMAT FOR ALL THE PERIODS THAT A TEACHER WORKED?" I am pleased that you found your answers but your headaches are self-inflicted. FM Forum's search feature is something you should become quite familiar with.

Hi Lee, IIUC, Pascal works in Administration and not directly with children. But with PhD, he should still understand the example you provided.

LaRetta

[edudna]

Dear experts,

Just take it easy and relax a little in this forum. I am trying to do my best sometimes I fail sometimes I succed. However always I respect the experts of FM that give me advise. What else can I do, it is just a forum. I hope someday to give help to other FM students and beginners from this forum and not always to ask. It is not a pleasant activity for me believe me.

Pascal

[edudna]

Dear FM experts,

In the example I give below I have two problems.

1. I want a user not allowed to give a period of time that was previously checked in another record. Example:If a period between 2006-2008 checked client should not be able to check any date between 2006 and 2008 in another record.

2. In a summaryofDates field I give I want to calculate all the days that checked in all the records. I used Let and Sum functions but as you see I have failed. If it is possible I want also this field to be autocalculate not a summary field.

Greetings

Pascal

test_summary.zip

[LaRetta]

Hi Pascal,

I'm not surprised to see this subject back. Take a look at the attached. You need to 1) calculate the number of days for each record (DateEnd - DateStart). And then a summary will add all the records' total days together. I've given you both a Summary field and a Sum() calculation, since you don't want summary (why you don't, I have no idea). See if that helps you.

I've assisted you by combining the threads again. No, don't thank me.

test_summaryREV.zip

[edudna]

Thank you for your response,

As you can see the problem that I have put is not the same as the above and it was not necessary to be merged.

However the problem is not exactly how to calculate the total of dateEnd-dateStart, thank you for the sum calculation anyway. The real problem here is to calculate spaces (DateEnd-DateStart) that were not previously checked.

[LaRetta]

You have posted exact duplicate posts repeatedly even after I had just messaged you privately not to. You have posted duplicate posts which you ADMIT are duplicates and say you just can't help yourself. It is not surprising then that I assumed you were doing it again; particularly with the sample file you presented. Your Point #2 (and the file) is exactly as you've been requesting here. Thus I moved them. I will not apologize for it.

For being an Educator/Administrator, you are not clear on expressing your requirements so let me see if I can express them for you:

You want to prevent overlapping date ranges.

You don't need anything from Point#2 at all.

In fact, you don't need a calculation but you want a VALIDATION

If so, see if the attached gets you closer. But this is the last assistance I shall provide you. Read up on Validation in FM Help. This Validation will take some homework on your part.

LaRetta

test_summaryREV2.zip

[edudna]

La Retta

TO FM OR NOT TO FM

THIS IS THE QUESTION:

[edudna]

To FM or not to FM. This is the question...

[AudioFreak]

I would say don't give up on Fm cuz of this. Just try to be complete in your requests for help. Between here and The Cafe you can pretty much do anything with FM. Give it time you will learn and be able to help people yourself.

In the meantime take the advice and try what people here are suggesting.

[edudna]

The only problem I have is that I put many questions here and have to study FM in order to not say the same thinks all the time. But I do not have time to study enough and I learn everything with trial and error method. However I am pursuing in a big educational database alone as I know that nobody in my country attempt to do something like me. As a consequense there is a big delay in all the educational administration processes and avtivities. I am an educational recearcher not an expert and I ask for your comprehention.

Pascal