Problem with calculations involving timestamps.

[Forum Joe]

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, 2007 by Guest

[Inky Phil]

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

[sbg2]

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

[Forum Joe]

Thanks. That's exactly what I want.