# Convert Variance in project sheet to weeks

✭✭✭
edited 08/27/24

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)

Tags:

• ✭✭✭✭✭✭

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

• ✭✭✭✭✭✭

@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.

Certified Platinum Partner

2023 Partner of the Year

PrimeConsulting.com

• ✭✭✭

@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

• ✭✭✭✭✭✭

@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"

Certified Platinum Partner

2023 Partner of the Year

PrimeConsulting.com

• ✭✭✭

Matt Lynn-PCG

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

im getting an invalid operation

• ✭✭✭✭✭✭

What does this do for you? Same error?

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

• ✭✭✭✭✭✭

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

• ✭✭✭

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.

• ✭✭✭✭✭✭

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

• ✭✭✭✭✭✭

Happy to help!😁

#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.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!