Formula involving Today() is returning the wrong number

Options

I have a formula in my project plan summaries to keep track of how many days are remaining in a project.

The formula is (=Finish3 - TODAY()). This formula works in other sheets. I have one sheet where it is not functioning properly.

Finish3 is a date cell that is a rollup of the finish dates of all tasks in the project. It contains the farthest date recorded in the finish column.

the formula Finish3 - TODAY() is currently returning 168. Today is 11/27/23. Finish3 contains the date 5/13/24.

When I put those dates in other cells and get the duration between them, it returns 121d. Why is that formula returning a different number?


I have tried refreshing the sheet. I created another field that just contains the TODAY() function and linked the formula to that. I'm just confused about what's going on here.


Any help or insight would be greatly appreciated

Tags:

Answers

  • Zachary Hall
    Zachary Hall ✭✭✭✭✭✭
    Options

    Hi @p.moore,

    168 would be correct. What is the exact formula you are using in those other cells?

    Best,

    Zach Hall

    Training Delivery Manager / Charter Communications

  • p.moore
    p.moore ✭✭
    Options

    Thanks for the response - you're definitely right @Zachary Hall


    Looks like another user may have changed the Working Days & added baselines to my template sheet. I think the Working Days may be the issue here. When I changed the working days, all the dates in the sheet changed.


    Could Working days be the issue here? My template has all seven days as working days but the sheet with the issue has only MTWThF.

  • Zachary Hall
    Zachary Hall ✭✭✭✭✭✭
    Options

    Yes. 168 working days is exactly 24 weeks. 168d-48 (24 weeks * 2 days) =120d. More than likely the one is in there because it is including today.

    Best,

    Zach Hall

    Training Delivery Manager / Charter Communications

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!