Formula Assistance

I’m sure I’m not the first to ask this. I could not find a post addressing my question.

I am creating a sheet to track invoices through a department; receive to payment.

I created a check box column titled 'January - Invoice Received' when the check box is checked i want the next column over titled 'January - Invoice Received (Date)' to display the date the check box is checked.

I have the 'January - Invoice Received (Date)' column type set to date and the formula I have is =IF([January - Invoice Received]3, TODAY()). The problem i have is when the sheet is re-opened all cells in the 'January - Invoice Received (Date)' column with entries update again to the current date.

Any help would be appreciated.

Thanks

Find more posts tagged with

Finance

Formulas

Government

IT & Operations

Project Management

Comments

J. Craig Williams

Thomas,

See if this will help:

https://community.smartsheet.com/discussion/formula-selecting-checkbox-will-populate-completion-date

Craig

Thomas Muscarello

Craig,

Sorry for the late response. I see the workaround in the solution you posted. The problem with the sheet I created it is going to be used for tracking work flow pertaining to invoices on a monthly basis. I wasn’t really clear with that above.

For account 112233 I will have columns January received; January received Date; January paid; January paid date. The check box feature will be in columns January received and January paid. When one of them is checked it will place the date in the column for that month. This way can track time between received and paid and which bills are at which stage.

I did create a similar solution on a different sheet since my last post which works.

=IF(Status44 = "Done", TODAY())

If my status column changes to done it will update the date completed column with the date the status was changed. Once the sheet is refreshed the date does not update as it does when a check box is used. If I change the status it will remove the date. I will probably just edit my other sheet and use the drop down selection instead of a check box.

The solution you provided will work for a few other sheets I have.

Thanks

J. Craig Williams

Thomas,

Are you saying that this

=IF(Status44 = "Done", TODAY())

is not updating?

The IF should not be looking for change, only for value.

When I open up the sheet tomorrow, it should show tomorrow's date (assuming Status44 = "Done")

I'll need to investigate that.

Craig

Thomas Muscarello

Craig,

Unfortunately, i was wrong, some of the cells had the folmua and some didnt which led me to believe it was working. I will look to see how to remove my suggestions. Thanks

J. Craig Williams

Thomas,

I assumed so, that would have been a horrible bug, even though it seemed to fix your problem.

Now, back to your original (and I assume, current) probem.

Can you describe it again, so that I don't get confused?

(There's no guarantee I won't be confused, but it might help)

Craig