Does someone know how I can make a time calculation field not count during saturday and sunday ?

I have a system for a callcenter that registers the time the client calls, the reason and when the attendants answer the client

Off the top of my head (untested),

something like the following pseudocode should work:

RawDays = EndDate - StartDate

Remainder = Mod(RawDays, 7)

FullWeeks = (RawDays-Remainder) / 7

Cover = DayOfWeek(StartDate)+Remainder

ExtraEnds = Case(Cover >=7, 2, Cover=6, 1, 0)

Days = RawDays - (2*FullWeeks) - ExtraEnds

Here is another one to play with:

((DateEnd - DateStart) * 86400) + TimeEnd - TimeStart

HTH

Lee

Hi there,

Just before I jump into the pool....

Why not...

((DateEnd - DateStart [color:"red"] -((weekofYear(DateEnd)-weekofYear(DateStart))*2))) * 86400) + TimeEnd - TimeStart

Ugo, I think that will work as long as we are guaranteed that no Start and no End will ever be during the weekend. Logically, they shouldn't be... but if they have a convention of putting a Saturday night end in certain situations, that would have to be fixed (or the more complex formula used).

Same issue with the Time calculations in general... plus we need to talk about shifts if we are computing seconds rather than days. Most shifts don't run midnight to midnight, as the latter two computations assume. But, if we can assume the start and end times will be during the working shift, then we can easily accommodate any single shift (e.g., 8 to 5).

Ugo,

I would like to thank you all for helping me.

Ugo, It worked and I would like to thank you for helping me. But I would also like to make sure I undestood what the calculation does.

In case I only don

Correct. You don't even need to multiply it then. But the logic stands here.

As you understood, weekofYear(DateEnd)-weekofYear(DateStart) will determine if the answer was made in another week.

By a multiplication by 2, you'd get the 2 days (Saturday and Sunday off).

Reducing by one would be OK for only Sundays.

Thanks,

And if the start date can be saturday or sunday,

but it shouldn

Ugo,

I hadn

Case(Year(g_endDate)=Year(g_startDate), old formula, new formula)

Basically, the new formula would calculate the delay from Dec 31 to your start date and add it to a calculation of your end date - 01 Jan of g_start date +1. I think the Time wouldn't change...

((Date(12,31,Year(g_date)) - DateStart -((weekofYear(Date(12,31,Year(g_date)))-weekofYear(DateStart))*2))) * 86400) +

((g_endDate - Date(01,01,Year(g_endDate))-((weekofYear(g_endDate)-weekofYear(Date(01,01,Year(g_endDate)))*2))) * 86400)

+ TimeEnd - TimeStart

At least, try it out...

OO...

I went a little fast, and messed up with field names. So here's a clean one (not tested).

((Date(12,31,Year(Startdate)) - StartDate-((weekofYear(Date(12,31,Year(StartDate)))-weekofYear(StartDate))*2))) * 86400) +

((EndDate - Date(01,01,Year(EndDate))-((weekofYear(EndDate)-weekofYear(Date(01,01,Year(EndDate)))*2))) * 86400)

+ TimeEnd - TimeStart

And what about holidays and DaysOffs???

Ugo DI LUCA said:

OO...

I went a little fast, and messed up with field names. So here's a clean one (not tested).

((Date(12,31,Year(Startdate)) - StartDate-((weekofYear(Date(12,31,Year(StartDate)))-weekofYear(StartDate))*2))) * 86400) +

((EndDate - Date(01,01,Year(EndDate))-((weekofYear(EndDate)-weekofYear(Date(01,01,Year(EndDate)))*2))) * 86400)

+ TimeEnd - TimeStart

And what about holidays and DaysOffs???

Ugo, your calculation has an extra ) in it right here "*2)) [color:"red"]) * 86400) + "

I agree with Ugo about the Holidays and Personal Days, Etc.

This usually requires a holiday file of somesort. See Bob Weavers' Example in the Simple File area. I think it was called "Workdays"

HTH

Lee

Lee Smith said:

I agree with Ugo about the Holidays and Personal Days, Etc.

This usually requires a holiday file of somesort. See Bob Weavers' Example in the Simple File area. I think it was called "Workdays"

Typo Typo....

Where's that Simple File Area ??

If really I could make things simple....

Thank you very much for your help. (both Ugo and Lee)

About the holidays...For now it

Hi LVA,

How about posting the times and dates for any that are not working so I (we) can see what you are referring to.

Lee

Ok.

These two for example don

Right you are. May be breaking this calc in a more understandable structure would help identify the problem.

While surely Lee is trying to break it and find the clue...

So

SD = Startdate

YS = Year Start--> Year(Startdate)

ED = Enddate

YE = Year End--> Year(Enddate)

LDYS = Last Day of YS --> Date(12,31,Year(Startdate)

FDYE = First Day of YE --> Date(1,1,Year(Enddate)

WSD = Week of StartDate --> weekofYear(SD)

WED = Week of EndDate --> weekofYear(ED)

LWSY = Last Week of Start Year --> weekofYear(LDY)

FWEY = First Week of End Year --> weekofYear(FDYE)

((LDYS - SD-((LWSY-WSD)*2)) * 86400) +

((ED - FDYE-((WED-FWEY*2)) * 86400 )

+ (TimeEnd - TimeStart )

or

Last Day of Year Start - Start Date - ((Last Week of Start Year - Week of Start Date)*2)* 86400

+

End Date - First Day of End Year - ((Week of End Date- First Week of End Year)*2)*86400

+

(Time End - Time Start).

From your example :

((31/dec/2002-31/dec/2002 - ((53-53)*2))*8600 ----> ((0-0)*8600) =0

- ((28/01/03-01/01/03)- ((5-1)*2))*8600 ----> (28-1)-((5-1)*2)*8600

+ Time end - Time Start.

Basically, I THINK the error comes from the calculation for the next year, as the gap from 01/01/03 to 28/01/03 should be 28 days rather than (28-1)

So why not change the calc including Day of Year instead of Date End - First Day of Next Year.

((Date(12,31,Year(Startdate)) - StartDate-((weekofYear(Date(12,31,Year(StartDate)))-weekofYear(StartDate))*2)) * 86400) +

((DayofYear-((weekofYear(EndDate)-weekofYear(Date(01,01,Year(EndDate)))*2)) * 86400)

+ TimeEnd - TimeStart

Mind giving it a try also ?

Post deleted by Ugo DI LUCA

With so many separators and parenthesis, it turned wrong. Here's one I finally tested.

Case(Year(dateEnd)=Year(dateStart),Old Formula, (((Date(12,31,Year(dateStart)) - dStart)-((weekofyear(Date(12,31,year(dateStart)))-weekofyear(dateStart))*2)) * 86400)+(DayofYear(dateEnd)-(WeekofYear(dateEnd)-

WeekofYear(Date(01,01,Year(dateEnd))))*2)* 86400+ timEnd - timStart)

Now, this calc works for 24 work hours days...!!!

I tried it and it worked.

Thanks for breaking it up, it really helped me understand.

Thanks,

LVA