So to calc a completion date in excel that includes the NTP date itself is @sum((A1+A2)-1) because excel does not count the NTP date in that formula. With a A1 being the NTP date and A2 being that Calendar Days. Are there any other, or different formulas you use to quickly calc dates in excel. (I see date off by 1 all the time.)
Schedule date calcs in Excel
Forum Sponsor
Top Posters
Julian Pegg
1 posts
Peter Nagy
2 posts
Raymund de Laza
17 posts
Syed_Asad
0 posts
Tony Greyvenstein
0 posts
Ahmed Al-Jubouri
13 posts
Umar Alvi
3 posts
Sibusiso Mahlalela
0 posts
Michael Samanyayi
3 posts
Simon Gumede
0 posts
one way in excel is to make the NTP date 1 days less than what it is in p6 and all dates wil then work out to match p6
so all you need to do is adjust the starting date which is NTP
Use date-hour format for granularity.
Try Date Formulas showing date-hours
Thanks Santosh, Typical NTP contract interpretation is using Finish Milestone and not Start Milestone that is why the Contract defines NTP + ? days = completion date. That's why I am confused on the formula being used. Also if the data is coming from P6 using copy paste or export (it depends on the setting and layout of the user) whether the time is shown or not otherwise the data is always whole number format or 12:00AM therefore in excel it always count as 1 day and not a fraction of a day.
Rodel, It does depend on the nature of the task being assessed. Generally, a one day task in scheduling will be the same date, consider a task starting 1-Jan at 08:00 and of duration one day. The date will still be 1-Jan, but the time will be 17:00.
In Excel, a value of 1 is calculated as a whole 24hr Day. so to check how calcs are being performed in Excel, you will need to display the number format to include hh:mm
John, a few I use:
Excel is good for dates, just make sure you view the time also as scheduling tools may export the date & time as a fraction of a day, and this can through Excel calcs out.
Not sure how are you interpreting the formula and your understanding of NTP + ?days (completion date). To make it simple lets say NTP + 1 day = completion date. Say NTP = 1 Jan 2022 + 1 day = 2 Jan 2022. If I used your formula, @sum((NTP + 1 day) - 1), the completion date will be still 1st Jan 2022? which make no sense.