Ok, I'm working on a simple job tracking/ticketing system. When someone creates a job, it's given a "due time". I've got another field which is "days of job. I've got a "Due time" field which is a timestamp, calculated on creation. The calculation is:

=CreatedOn+(DaysOfJob*24*3600)

Thus, if DaysOfJob is 1, the due date will be 24 hours after the creation date.

This works fine, but I'm trying to extend it a little, which is where the problem occurs.

The Problem

This is for a business that is only open 9 to 5, 5 days a week. Thus, if someone creates a job of with a length of 1 on a Friday, I want it to the due date to be Monday. (Ie, skip saturday and sunday in all calculations.)

half-solution a)

When I started this post, I figured that I'd just detect if the resultant day is a saturday or a sunday, and then just add 2 to the job. Trying to work on this calculation, I'd do something similar to:

Let(x=CreatedOn+(DaysOfJob*24*3600);If(DayOfWeek((x))=1;x+(24*3600);x))

Now, this isn't working, and the DayOfWeek(x) test is never passing. I'm pretty sure this is because x is just one long number until it gets returned to the function when it gets converted to a timestamp, and you can't do a DayOfWeek(x) on a single digit. Can anyone solve the problem this way? Anyway to force (x) to a timestamp format first?

the second problem

Of course, thinking it through as I write this post, I realise that even if the problem is solved the above way, it's not the best solution. Since if you set a job length of 3 on a friday, it will still set the due day as Monday.

Can anyone rewrite the calculation to always take into account the weekends?

Edited June 20, 200718 yr by Guest

You might use another (unstored) calc field for your display.

The calc for the field should be along the following lines

Case(DueDate = "Saturday" or DueDate = "Sunday"; "Monday";DueDate)

Not sure about the exact syntax cos it depends on the field types. Gives you the idea though

HTH

Phil

If I'm interpreting your calc correctly:

If the CreatedOn = Wed. 6/20 and DaysOfJob = 5 your calc would return Monday 6/25 rather than Wed. 6/27.

Attached is what I came up with in version 7. It includes the calculations broken out into seperate fields but also includes the full calc as one field. I don't have time to fully test it right now. If anyone is going to use it I suggest they do some extensive testing.

Timestamp_Days.zip

Thanks. That's exactly what I want.