Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Date and Hours


Plextor
 Share

Recommended Posts

Hi !

I have been looking for different solutions on my problem but cant get any work.

Our workday is between 07:00 and 16:00 with 1 hour dinner.

We build components and i want to calculate the days and time for building

the component. I have the startdate, starttime and the start timestamp.

I also have the end date, endtime and end timestamp. Let´s say i start building 2005-02-03 09:00 and finishing 15:30 2005-02-07. I have to remove saturday and sunday. I wish to have the result in how many workinghours i spent on the component. I also need the result in workdays and workhours.

I have version 7. I hope i for a solution, but perhaps i want more than possible . Thanks for a nice forum.

Link to comment
Share on other sites

Why note a Shift Start TimeStamp and a Shift End TimeStamp and a Daily Total TimeOfTimeStamps = Shift End TimeStamp - Shift Start TimeStamp

 

Then through a relationship (JobID)

Sum(RelationshipName::Total TimeOfTimeStamps)

Link to comment
Share on other sites

Hi Plextor,

 

Well I can't help myself ... this is a very illogical request unless you can provide some sound rules. If your start time is 11:00 AM, do you still take an hour lunch? If your EndTime is 2:00 PM, do you still deduct an hour lunch? What if you only work an hour on your starting day? See the problems here?

 

And do you truly only want rounded hours? That could be a good thing for your customer if you're over by 3/4 of an hour. It could also be upsetting. We need to know whether you wish to round up or down and by 1 hour, 30 minutes, 15 minutes and so on. smiley-laughing

 

Oh. And what if this span wraps over Christmas? Are you going to count that time as well? As you can see, we have more questions than answers at this point ...

 

Truly, if you properly clocked in and out each day it would be a piece of cake for you.

 

L

Link to comment
Share on other sites

Well this makes some assumptions ... it assumes that if you started after 11:00 AM that you didn't take a lunch that day; and if you stopped before 11:00 AM, you didn't take a lunch that day. And it rounds to full hours. I attempted to list the calc out fully so you could easily see (and hopefully understand) each aspect.

 

But other questions arise, such as if you start on a weekend. The calc will count starting or stopping on a weekend but NOT any weekends inbetween. You will need to pull in a holiday table to protect from Christmas or holidays. And you didn't indicate how you wanted it displayed, so I listed it as ___ days ___ hours.

 

Let ( [

startDay = GetAsDate ( StartTS ) + 1 ;

startTime = GetAsTime ( StartTS ) ;

endDay = GetAsDate ( EndTS ) - 1 ;

endTime = GetAsTime ( EndTS ) ;

lunch = 3600 ;

firstDay = Time ( 16 ; 0 ; 0 ) - startTime - If ( startTime

lastDay = endTime - Time ( 7 ; 0 ; 0 ) - If ( endTime > Time ( 11 ; 0 ; 0 ) ; lunch ) ;

fullDaz = endDay - startDay - PatternCount ( "17" ; DayOfWeek ( startDay ) ) + 1 ;

xHour = Div ( firstDay + lastDay ; 3600 ) ;

d = Div ( xHour ; 8 ) + fullDaz;

h = Mod ( xHour ; 8 ) ] ;

 

If ( d ; d & " day" & If ( d > 1 ; "s" ) & " " ) &

If ( h ; h & " hour" & If ( h > 1 ; "s" ) )

)

 

LaRetta

Link to comment
Share on other sites

Oh, the point of this hairy calc is this ... if you had each day as a time record with start timestamp and end timestamp and then a calc (result is time) in a timetable as records with ...

 

EndTS - StartTS - 3600

 

... then you can do as Robert suggests and let the relationship group and total your job time-spans. It would make things much easier for you. But then maybe this is so you can determine the length of job as an ESTIMATE and you don't want to clock in and out every day. But you'll need to clock out for holidays and can't clock back in for that day - at least before lunch (err, dinner). Oh Really!

 

Let us know if you need it adjusted. smiley-laughing

Link to comment
Share on other sites

Some more explanation. Our workingtimes are 07:00-12:00 then 1 hour dinner, the 13:00-1600.

I start create a component 07:00 on monday. I have finished the component on wednesday 15:30. Then i start create a new component 15:30, and i have finished that component on monday 14:05. Then i start on next......

They always have a button pressed in filemaker when start and when finish a component. So i made fields Startdate,starttime,startstamp,enddate,endtime and endstamp. I also made some global fields like worktimeStart, worktimeEnd and dinner.

Startdate:2005-01-01 - date

Starttime:07:00 - time

Startstamp:2005-01-01:07:00 - timestamp

EndDate:2005-01-03 - date

Endtime:15:30 - time

EndStamp:2005-01-03:15:30 - timestamp

Worktimestart:07:00 - time

WorktimeEnd:16:00 - time

 

Weekends have to be removed. But christmas and other are not so important.

I hope this explained some.....perhaps to difficult for me to do this calc, but perhaps with your help :)

Link to comment
Share on other sites

This one worked perfekt. Since i dont understand all calc would it be good if you cold also verify with what i explained in my earlier post. Would also be nice with minutes :)

Let ( [

startDay = GetAsDate ( StartTS ) + 1 ;

startTime = GetAsTime ( StartTS ) ;

endDay = GetAsDate ( EndTS ) - 1 ;

endTime = GetAsTime ( EndTS ) ;

lunch = 3600 ;

firstDay = Time ( 16 ; 0 ; 0 ) - startTime - If ( startTime

lastDay = endTime - Time ( 7 ; 0 ; 0 ) - If ( endTime > Time ( 11 ; 0 ; 0 ) ; lunch ) ;

fullDaz = endDay - startDay - PatternCount ( "17" ; DayOfWeek ( startDay ) ) + 1 ;

xHour = Div ( firstDay + lastDay ; 3600 ) ;

d = Div ( xHour ; 8 ) + fullDaz;

h = Mod ( xHour ; 8 ) ] ;

 

If ( d ; d & " day" & If ( d > 1 ; "s" ) & " " ) &

If ( h ; h & " hour" & If ( h > 1 ; "s" ) )

)

Link to comment
Share on other sites

Minutes? Are you suggesting that this calculation could ever produce an accurate result? Surely (in that span), you've taken 1 1/4 hour lunch; worked 15 minutes over and so on. This cracks me up but minutes it shall be. smiley_cool

 

I rewrote the calc. I had mixed styles and the problem with that approach is that ANY change causes a rework whereas a good calc would easily adapt. We are mixing days & times so I pulled fullDaz back into the total minutes (green) which simplified the process. I adjusted your dinner (lunch?) to 12:00, included minutes and it will fit your requirements. It is cleaner but NOT elegant:

 

Let ( [

startDay = GetAsDate ( StartTS ) + 1 ;

startTime = GetAsTime ( StartTS ) ;

endDay = GetAsDate ( EndTS ) - 1 ;

endTime = GetAsTime ( EndTS ) ;

lunch = 3600 ;

firstDay = Time ( 16 ; 0 ; 0 ) - startTime - If( startTime

lastDay = endTime - Time ( 7 ; 0 ; 0 ) - If ( endTime > Time ( 12 ; 0 ; 0 ) ; lunch ) ;

fullDaz = endDay - startDay - PatternCount ( "17" ; DayOfWeek ( startDay ) ) + 1 ;

sumMin = fullDaz * 480 + Div ( firstDay + lastDay ; 60 ) ;

d = Div ( sumMin ; 480 ) ;

h = Div ( sumMin - d * 480 ; 60 ) ;

m = Mod ( sumMin ; 60 )

] ;

If ( d ; d & " day" & If ( d > 1 ; "s" ) & " " ) &

If ( h ; h & " hour" & If ( h > 1 ; "s" ) & " " ) &

If ( m ; m & " minute" & If ( m > 1 ; "s" ) )

)

Link to comment
Share on other sites

Hi again !

I still have problems with getting rid of Saturday and Sunday. It seems like one of the days is still in the calculation. Do you have any more ideas ?

Thanks for helping :)

Link to comment
Share on other sites

Hmm, yes. Well I guarantee this one works in ALL situations. The problem wasn't the span (it only took spanStart, spanEnd and wkndCount) but rather determining the logic of 'what is a day.' If you work 3 hours in one day, did you work one day or 3 hours? Most of this calc deals with resolving that logic. Since I didn't know (on each day) the exact worktime, I deducted an hour for lunch if they worked over 4.5 hours in a day. We are also mixing mathematics and display issues. To eliminate minus numbers from the display (and transfer those minus seconds forward), I used > 0 test. Calculation result of text:

 

Let ( [

startDate = GetAsDate ( StartTS ) ;

endDate = GetAsDate ( EndTS ) ;

startTime = GetAsTime ( StartTS ) ;

endTime = GetAsTime ( EndTS ) ;

spanStart = startDate + 1 - DayOfWeek ( startDate - 7 ) ;

spanEnd = endDate + 8 - DayOfWeek ( endDate ) ;

wkndCount = Div ( spanEnd - spanStart + 1 ; 7 ) * 2 - (spanStart endDate ) ;

wkSecs = ( endDate - startDate + 1 - wkndCount ) * 28800 ;

adjTime = Let ( [ day1 = GetAsNumber ( Time ( 16 ; 0 ; 0 ) - startTime ) ;

day2 = GetAsNumber ( endTime - Time ( 7 ; 0 ; 0 ) ) ] ; Min ( 28800 ; day1 - ( day1 > 16200) * 3600 ) + Min ( 28800 ; day2 - ( day2 > 16200 ) * 3600 ) ) ;

adjSecs = wkSecs + adjTime - 57600 ;

d = If ( Div ( adjSecs ; 28800 ) > 0 ; Div ( adjSecs ; 28800 ) ) ;

balDayNum = adjSecs - d * 28800 ;

h = If ( Div ( balDayNum ; 3600 ) > 0 ; Div ( balDayNum ; 3600 ) ) ;

balHourNum = BalDayNum - h * 3600 ;

m = If ( Div ( balHourNum ; 60 ) > 0 ; Div ( balHourNum ; 60 ) )

] ;

If ( d ; d & " day" & If ( d > 1 ; "s" ) & " " ) &

If ( h ; h & " hour" & If ( h > 1 ; "s" ) & " " ) &

If ( m ; m & " minute" & If ( m > 1 ; "s" ) )

)

 

If start or stop timestamps are fired on a weekend, that weekend time is ignored. I know you said you don't work weekends but Eddie Murphy Jr. says someday it will happen. If you want to count that time (in case you ever start or stop on weekend), just let me know and I can adjust it for you.

 

UPDATE: I should mention that you may not always see what you expect. In the example you provided, starting Wed 3:30 PM and ending Monday 2:05 PM will NOT display 4 days. Because you didn't work a full 8 hours on the project for either day. Instead, it will calculate as:

 

Wed - start 3:30 PM and end 4:00 PM (without lunch) = 30 minutes

Thurs - 8 hours

Friday - 8 hours

Monday - start at 7:00 AM and end 2:05 PM (with lunch) = 6 hours 5 minutes. So total is: 2 days, 6 hours, 35 minutes.

 

LaRetta smiley-smile

Link to comment
Share on other sites

 Share



×
×
  • Create New...

Important Information

Terms of Use