Convert Variance in project sheet to weeks

Maz Uddin

Hi

I'm using 5 days a week in my project sheets and I want to convert the variance column into weeks

baseline in weeks in working out the duration between the baselines dates (please ignore)

please can you advise the best way?

@Paul Newcome @jmyzk_cloudsmart_jp @Matt Lynn-PCG

jmyzk_cloudsmart_jp

Hi @Maz Uddin

[Variance in Weeks] =INT(Variance@row / 5) + "w " + (Variance@row - INT(Variance@row / 5) * 5) + "d"

https://app.smartsheet.com/b/publish?EQBCT=39ed28e9d73c4a5580dbb3db022b8f6e

Matt Lynn-PCG

@Maz Uddin That baseline and variance column are connected to the baseline feature indicated by the little symbols on the column header. Assuming you're leaving them connected (and not turning them off), you cannot make any format changes to that column.

If you were to turn off the baseline feature (which would likely defeat the purpose) and convert it to a regular column you "could" put a formula in there that would calculate the difference in days, divide that by 5 or 7 which would give you a decimal value. Then convert the decimal into days etc. Concatenate that with a "W" and "D" behind those values. It would be a hefty formula because it would have to account for +/- values and the 0 situations and some rounding. Also you'd have to build in that formula if you were capturing work days or week days.

Or I suppose you could use a left() or mid() function to pull the days value out of the variance column and then do the math in that column and keep the baseline feature on.

All in all I think there's a decent amount of risk with this that you'd have to really test to make sure you've captured all the scenarios. I'd recommend using days and not going down that path, but it could be done.

Maz Uddin

@Matt Lynn-PCG i was thinking to add a helper column and have a formula run in the background.

I have PMs who prefer reporting on this as weeks hence why I wanted that option.

I just want to show the 26 days as 5w 1d

Matt Lynn-PCG

@Maz Uddin IF you just want to use the variance that is there this would be the simple way (in a new column but not changing the actual connected variance column)

=ROUND(VALUE(SUBSTITUTE(Variance@row, "-", "")) / 7, 1) + "w"

OR if you wanted it a little more detailed with days and weeks you could use this:

=ROUND(VALUE(SUBSTITUTE(Variance@row, "-", "")) / 7, 0) + "w" + " " + ROUND(7 * VALUE(MID(ROUND(VALUE(SUBSTITUTE(Variance@row, "-", "")) / 7, 2), FIND(".", ROUND(VALUE(SUBSTITUTE(Variance@row, "-", "")) / 7, 2)), 3)), 0) + "d"

Maz Uddin

Matt Lynn-PCG

i changed the 7 to 5 because my project sheet doesnt count saturday and sunday

im getting an invalid operation

Paul Newcome

What does this do for you? Same error?

=INT(Variance@row / 5) + "w " + MOD(Variance@row, 5) + "d"

jmyzk_cloudsmart_jp

Hi @Maz Uddin

[Variance in Weeks] =INT(Variance@row / 5) + "w " + (Variance@row - INT(Variance@row / 5) * 5) + "d"

https://app.smartsheet.com/b/publish?EQBCT=39ed28e9d73c4a5580dbb3db022b8f6e

Maz Uddin

Paul Newcome Thank you for responding. I got an error code with this one though.

jmyzk_cloudsmart_jp Thank you mate! the formula worked!

appreciate the responses as always, really means a lot.

Paul Newcome

@Maz Uddin Which error code exactly? I tested it in my sheet, and it worked just fine. Curious to know what the differences are.

jmyzk_cloudsmart_jp

@Maz Uddin

Happy to help!😁

@Paul Newcome

#INVALID DATA TYPE (MOD part), #INVALID OPERATION (Whole formula)

I initially thought of using the same formula as yours.

It appears that the Variance column generated by the Baseline feature might not contain numerical data. For instance, the following formulas function correctly:

=INT(Variance@row / 5) + "w " + MOD(Variance@row ^ 1, 5) + "d"

=INT(Variance@row / 5) + "w " + MOD(Variance@row / 1, 5) + "d"

It seems that numerical operations like ^1 or /1 can convert special non-numeric values into numbers.