Round up date calculation to whole number (for text objects)

[#1]

I created a variable with the below formula: =vTotalWeeks

=(Date#([Project End Date], 'MMM DD, YYYY') - Date#([Project Start Date], 'MMM DD, YYYY')) / 7

It calculates correctly, but I need this to always round up to the next highest integer. So, 8.642343 should become 9.

Note: I am using text objects so I can't leverage the Number tab of a chart object.

[sunny_talwar · Accepted solution]

Try the Ceil() function

=Ceil((Date#([Project End Date], 'MMM DD, YYYY') - Date#([Project Start Date], 'MMM DD, YYYY')) / 7)

[#3]

Thanks Sunny this worked. Just one more follow up if i could.

I added another formula: =vWeeksConsumed / vTotalWeeks

This is made up of the below formula. I need this to return % with 2 decimals. 54.95% for example.

=([Week End] - [Project Start Date]) / ([Project End Date] - [Project Start Date])

Thanks again for your help

[sunny_talwar]

You want still round it up??

If yes, then try:

=Num(Ceil(([Week End] - [Project Start Date]) / ([Project End Date] - [Project Start Date]), 0.01), '##.00%')

or

=Num(Round(([Week End] - [Project Start Date]) / ([Project End Date] - [Project Start Date]), 0.01), '##.00%')

[#5]

Thanks Sunny. I ended up going with the below....your advice was helpful!!

=num(($(=$(vWeeksConsumed)) / num(vTotalWeeks)),'##.00%')

[sunny_talwar]

Great, as long as I am helpful

[#7]

Hi Sunny, if you are still available I had one follow up to this direct item.

=Ceil((Date#([Project End Date], 'MMM DD, YYYY') - Date#([Project Start Date], 'MMM DD, YYYY')) / 7)

This formula is working most of the times, but I have encountered an issue where I believe I need to amend this.

I have an example where Project Start Date = 10/26/15 and Project End Date = 11/30/15....This works out to 36 days which divided by 7 = 5.14. This formula is returning 5 and for my purposes, I need it return 6 weeks.

Do you have any suggestions to accomodate?

[sunny_talwar]

Ceil usually takes it to the next nearest integer So something like this Ceil(36/7) = 6.  Is it not doing that?

[#9]

Not sure if you can see the snapshot below but the Total Weeks is returning 5 for this project and I was expecting 6 [36 / 7]. This in turn impacts the % Weeks Consumed and Remaining which are referencing this formula (variable).

I have reviewed a ton of info on the community especially around date calculations and just can't figure this one out.

One other question if I could. Below for % Budget Remaining, my output is .802943....I need this to be 80.29%. This is my first time trying the below type formula; I obviously have something just slightly wrong for it not to respect the ##.00%. Again, any advice is appreciated. You've been very helpful!

=1.00 - (num($(=$(vActualRev)) / sum([Original Rev Est]),'##.00%'))

[sunny_talwar]

Can you check what you get when you do a simple Subtraction of the two dates:

Num(Date#([Project End Date], 'MMM DD, YYYY') - Date#([Project Start Date], 'MMM DD, YYYY')), '#,##0.000000')

I have a feeling that this might give you 35 or less than 35. I think once we know what we are getting, we might need to add a 1 here to make sure we get the right number we are hoping for:

=Ceil((Date#([Project End Date], 'MMM DD, YYYY') - Date#([Project Start Date], 'MMM DD, YYYY') + 1) / 7)

But this is pure speculation right now and will have to be tested out.

Let me know what you get and we can proceed from there.

Best,

Sunny