Calculate/Compute the duration between two dates in working days
Hello,
I'm back for more need in API tricks. Here's my case : because we can't edit the finish date of a task from the API, I need to find a way to make a task end at the date I want. For this, I either have the beginning date of my task, so I just need to find the duration to put in the corresponding field to have my task ending at the finish date I want.
As I work with python, I used the datetime module to get the time difference between the two dates and that's the result of this operation I used to put in the duration field. But of course, you should already think it does not work since the duration in smartsheet is a duration in working days.
From what I see, I do think it would be painful to check for every week-end or holiday that is inside the duration to remove the good amount of days from the duration.
I hope there's a smart way to do it with Smartsheet.
Thank you so much for your time,
RedSPINE
Comments
-
Getting the data of the sheet or the row you should be able to see the current value in the column being used for the duration of the task. You could then increment or decrement this value and provide it in a request to update the duration column. This will update the finish date in your sheet automatically based on your working days that are set in the dependencies of the sheet.
For example, assuming you have working days of Monday through Friday, if your task is currently two days long you would see the value "2d" in your duration column. If you needed to add one more day to it you could provide a value of "3d" in a request to update that cell. If this task currently ended on a Friday Smartsheet would automatically move it to end on the following Monday.
-
Ok so I guess I will have to work around that. It really is annoying we can't change the Finish date from the API. Since we can from the webpage, I doesn't make much sense to me ...
The problem is I don't know if I want to add 1 more day to the duration, I only know when I want my task to be finished, and then I should calculate how many days I need in the duration considering I must not put days for nonworking days and holidays. For now my only solution consists of incrementing a datetime object day by day, and count the number of days I added which aren't nonworking days or holidays.
By the way, I don't understand how to access a specific sheet's ProjectSettings object. Could you provide me an example please ?
Thank you
-
Yes, I can understand the frustration with not be able to edit End Dates directly via the API. I did some searching for methods of calculating working days via Python and I found this StackOverflow post here. You could consider using a library like that post suggests to calculate the working days for you. It may help you figure out how many days to add into your duration.
As for Project Settings, that is part of the Sheet object. Since Python has the convention of using all lowercase letters and separating with underscores for attribute names we use that method. If you got the data of a Sheet and stored it in a variable you call sheet you would access the projectSettings by using sheet.project_settings.
-
Well thank you, using a combination of elapsed time and basic date operations I should be able to achieve what I want. I feel a bit stupid to not have found the project_settings, I might have tried everything but this one, the simpliest. Thanks !
-
I am glad you should be able to get things working. Please don't feel stupid. That kind of stuff happens to me all the time. The simplest answer usually wins.
