none
Adding duration in days to an elapsed-time task (and vice versa) RRS feed

  • Question

  • I'm using MS Project Standard 2013

    I have found that if you have an elapsed-time task (that is, a task that is defined with a duration in elapsed time), and you try to add a duration in days to (for example) the start date, it will not work regardless of whether you use the dateadd or projdateadd function.  Adding duration in edays works with both dateadd and projdateadd, however.

    Conversely, if you have a task with duration defined in days, adding duration in days to the start date works only if you use projdateadd (which is expected).  Adding duration in edays works only with dateadd.

    Oddly (IMO), even if you put in a manually entered custom date field, the results of adding durations in days or edays still depends on the type of duration of the task, even if start date is not referenced at all.

    I have a project where tasks are mixed work-effort tasks and elapsed-time tasks.  I'm trying to compare the duration for all tasks with a list of durations, some of which are days and some edays.  I'd like to use a formula that will properly add either days or edays to dates (for example start date) regardless of whether the task is defined as a work-effort task (in other words with duration in days) or an elapsed-time task (that is, with duration in edays), but I haven't been able to figure out how to do that yet.

    In addition, I'd like to be able to add either days or edays to the start date of a summary task, but to get the correct answer, it seems I have to know in advance whether the duration I'm trying to add is days or edays, because a different formula is required in each case.

    Can someone please provide advice?  Thanks!




    • Edited by KingStefan2 Thursday, January 2, 2020 4:11 PM
    Thursday, January 2, 2020 3:54 PM

Answers

  • KingStefan2,

    I think you've got the right idea, but you seem to have found a seam in the armor.  Project allows users to enter mixed elapsed and non-elapsed values in the custom duration fields for a task, but it looks like only the numeric values of these fields (in minutes) can be read by other functions (including vba).  The duration-type metadata remains hidden.  Overall, a text field would prove more useful.

    The simplest workaround I see is to copy/paste the custom duration column into a custom text column, say Text1, then use 

    NOT InStr(1,[Text1],"e")

    in lieu of {expression testing whether duration to add is in days}.  

    Good luck, tom

      

    • Marked as answer by KingStefan2 Monday, January 6, 2020 11:16 PM
    Friday, January 3, 2020 9:33 PM

All replies

  • Did some further experimentation and found that Summary tasks always appear to be in work days (is this always true?) and behave for the purpose of date adding like detailed tasks with duration in days.

    Milestone tasks also behave like tasks with duration in days if their duration is "0 days".  However, I noticed that if you make the duration "0 edays", them milestone tasks behave like elapsed-time tasks for the purpose of adding dates.  This is true even if the mark task as milestone box is  unchecked.


    • Edited by KingStefan2 Thursday, January 2, 2020 5:49 PM
    Thursday, January 2, 2020 5:46 PM
  • KingStefan2,
    Please share the exact formulas you are using.  I don't use MSP 2013, but I cannot duplicate your issue on either 2010 or 2016 (Pro).  Specifically, I can use DateAdd to add elapsed days to the start of any task, whether elapsed- or non-elaped duration.  I can also use ProjDateAdd to add either elapsed days or workdays to the same start dates. 

    Neither DateAdd nor ProjDateAdd accept any input toggling between "edays" and "days".  The first argument in DateAdd is always an elapsed time interval (with some adjustments for months/years), so DateAdd("d",10,[Start]) always returns a date value exactly 10 elapsed days after the task start. The second argument of ProjDateAdd is always in minutes, which may be elapsed or working depending on the calendar specified in the third argument of the function.  Omitting the calendar argument may be causing some unexpected results for you, since the default calendar for an elapsed-duration task is always an unmodified 24-hour calendar while the default calendar of a non-elapsed-duration task will typically be some version of the Standard (5dx8h) calendar. (IIRC, these defaults change slightly when employing the function in vba rather than a formula.  Also, some versions of Project Server do not recognize the calendar argument.)

    Though it's not clear exactly what you are trying to do (or why), I believe using ProjDateDiff with explicit calendar arguments will get you there.

    Yes, elapsed durations are not allowed for automatically-scheduled summary tasks.  Summary task duration units always match the "Duration is entered in:" schedule option for the project - the default is "days".

    Revert if you need more info.  Good luck, tom
    • Edited by Tom BoyleBPC Saturday, January 4, 2020 3:43 PM added PS note
    Thursday, January 2, 2020 8:01 PM
  • Thanks for your help, Tom.

    After reading your post and experimenting, I discovered that the difference in your results from mine is that I neglected to specify a calendar in the ProjAddDate formulas.  That answered part of my issue.

    In addition, that provided me a way to distinguish between elapsed-time tasks and normal tasks.  Specifically, if ProjDateAdd([start],[custom duration]) = ProjDateAdd([start],[custom duration],"standard"), then I know the task has the standard calendar; if not, then it has some other calendar.  In my case I have tasks that use only either the standard calendar or are elapsed-time tasks, so I can use the iif function to detect when a task is an elapsed-time task.

    So, I understand everything else you said.  Then problem is: I don't know a priori whether the duration I'm trying to add is in days or edays.  If it is in days, then I have to use ProjDateAdd with the standard calendar specified, and if it is in edays, I have to use the DateAdd function.  But I don't know which to use ahead of time, so I'm looking for formula that works in both cases.  Is there a way I can tell if the duration I'm trying to add is in days or edays?

    To explain further what I'm trying to do:  I have several applications, but this one will provide a good example.

    Above I said: "I have a project where tasks are mixed work-effort tasks and elapsed-time tasks.  I'm trying to compare the duration for all tasks with a list of durations, some of which are days and some edays.  I'd like to use a formula that will properly add either days or edays to dates (for example start date)..."

    To elaborate: the following is a set of sample data.  I want to know whether the Duration of the task is equal or not equal to the Custom Duration entry:

    Duration     Custom Duration     Same?

    14 edays     14 edays                  Yes

    19 edays     14 days                    Yes

    10 days       10 edays                  No

    10 days       11 days                    No

    19 edays     12 days                    No

    And so on.

    My thought was to use a formula something like:

    iif(iif({expression testing whether duration to add is in days},ProjDateAdd([Start],[Custom Duration],"standard"),DateAdd("n",[Custom Duration],[Start]))=[Finish],"same","not same")

    But I haven't a clue what to use for "expression testing whether duration to add is in days".

    Any thoughts you could provide would be greatly appreciated!  Thanks!


    • Edited by KingStefan2 Friday, January 3, 2020 5:20 PM
    Friday, January 3, 2020 4:41 PM
  • Alibi: I realize that in some cases there may be a day or a few days difference when comparing edays to days depending on the date we are adding to.  I got that, and generally I'm looking for differences greater than that, like on the order of 30 edays.  So that part is OK.
    Friday, January 3, 2020 4:49 PM
  • And, of course, when comparing days and edays, you are going to have fractional day issues, so I have to use something like:

    Date1=iif({expressing testing},ProjDateAdd([Start],[Custom Duration],"standard"),DateAdd("n",[Custom Duration],[Start]))

    iif(abs(datediff("n",[Date1],[Finish])/24/60)<0.5,"same","not same")



    • Edited by KingStefan2 Friday, January 3, 2020 6:11 PM
    Friday, January 3, 2020 5:18 PM
  • KingStefan2,

    I think you've got the right idea, but you seem to have found a seam in the armor.  Project allows users to enter mixed elapsed and non-elapsed values in the custom duration fields for a task, but it looks like only the numeric values of these fields (in minutes) can be read by other functions (including vba).  The duration-type metadata remains hidden.  Overall, a text field would prove more useful.

    The simplest workaround I see is to copy/paste the custom duration column into a custom text column, say Text1, then use 

    NOT InStr(1,[Text1],"e")

    in lieu of {expression testing whether duration to add is in days}.  

    Good luck, tom

      

    • Marked as answer by KingStefan2 Monday, January 6, 2020 11:16 PM
    Friday, January 3, 2020 9:33 PM
  • Thanks.  I appreciate your help.

    I had thought of looking for an "e" in a text version of the field, but was hoping there was a way to automatically generating the text instead of cutting and pasting.  I tried different things to no avail, like using cstr(duration1), but that just gives me text of the numerical value in minutes.

    So I guess I'm stuck with cut and paste!

    It's funny, because as you say, the metadata must exist internally.  Otherwise, how could Project correctly determine finish dates from a duration in edays?

    Thanks again!

    Friday, January 3, 2020 9:44 PM
  • Glad you got it sorted.  If one or more of my responses helped, you could close this thread by marking them as answers.

    If the custom duration field is so dynamic that copy/paste is too much trouble, you could easily automate that part with a vba/macro. (Once you start with vba, however, then you might as well populate the "same" field entirely with it.)

    Good luck, tom

     

    Saturday, January 4, 2020 4:38 PM
  • KingStefan2,

    Here's a fly on the wall idea. Assuming the "eday" tasks do not span normal working days only (i.e. Mon through Friday of a single week), a simple VBA routine could examine each day of the task and determine if it is a normal working day. As soon as it detects a non-working day (i.e. Saturday (7) or Sunday (1), a flag is set which can then be used to apply the appropriate formula.

    It could potentially also be done with a formula, but that might get a bit complex. Nonetheless, here is how it looks in a simple example.

    Please don't spray me with Raid.

    John

    Saturday, January 4, 2020 5:09 PM
  • John,

    Thanks for chiming in with that good idea.  Unfortunately, Stefan has two lists of task durations that he needs to compare, on a calendar/elapsed basis, each with a random mix of working and elapsed durations.  His issue ultimately comes down to the inability to read the duration format of a custom duration field using a formula in another custom field.  For Duration, we can get around this using the technique you propose or the one above where we make use of Project's default calendar selection.  These don't work for custom duration fields, however, since Project never uses them in the schedule.  (A vba solution would be easy but not necessarily what Stefan is looking for.)   

    t   

    Saturday, January 4, 2020 9:56 PM
  • Why so many elapsed duration tasks?

    Instead of using elapsed duration, why not use a 7 day calendar for the tasks that need it? Then there isn't a problem.


    Rod Gill
    Author of the one and only Project VBA Book and VBA developer.
    www.project-systems.co.nz

    Saturday, January 4, 2020 11:21 PM
    Moderator
  • Tom,

    Yeah, I admit I didn't pore through all the subtleties of KingStef2's process but when I saw the copy and paste, I cringed. Although he may be trying to reach his end goal with custom field formulas and manual effort, that sounds like a whole lot of inefficiency that begs for a more sophisticated approach (e.g. VBA). However, I think Rod makes a good point, maybe the approach he is attempting is "overcooked" and a more straightforward approach is better.

    By the way, yes KingStef2 should mark one of your responses as the answer, but doing so will not "close" the thread. You will get the deserved credit but the thread will still be open for additional input.

    John

    Sunday, January 5, 2020 4:28 PM
  • Thanks for your suggestion, Rod.

    Unfortunately, that won't solve my problem.  Comparing 7-day calendar task durations to an arbitrary custom duration field will still not help if I don't know what calendar the CUSTOM field is associated with.

    Monday, January 6, 2020 11:20 PM
  • Thanks, John.  You are certainly correct that when planning a project, the use of custom fields is certainly overcooked.  And I would never try to plan a project that way.  Very inefficient.

    But what I'm trying to do here is compare a list of values for a similar project that someone else has already planned, and has mixed edays with days.  That's not something I have control over - just a fact of life.  In fact, this is not really a project planning problem directly at all.

    One solution I thought of was to compute the datediff for finish-start in a copy of the original "similar" project file, and use those values to do the comparison.  That way I know that it's all elapsed time, and there is no ambiguity.

    Thanks everyone for your contributions.  Tom has provided the answer I was looking for ultimately, and that is that there is no way to tell a custom duration in edays from one in days without copying the column to a text field or something similar. That, plus including the calendar in projdateadd function calls is necessary to get predicable results.

    My question really turned out to be more academic than practical.

    Thanks again  - Stef




    • Edited by KingStefan2 Monday, January 6, 2020 11:34 PM
    Monday, January 6, 2020 11:27 PM