Time/Date Stamp based on Boolean Value, Formula only.

Spyclown

New Member
Joined
Dec 28, 2021
Messages
5
Office Version
  1. 2021
Platform
  1. Windows
Hi Folks,

I have a table with upwards of 1000 entries on it and multiple people will be working off this file (not at once).
The idea is simple, you check a check box and it adds a time stamp into the "Sent Date" column. Each box is linked to the neighboring cell.
However using the NOW() or TODAY() functions are volatile and refresh when a new entry is made or the workbook is closed and reopened. I have gotten close using IF commands, but am always faced with errors.

Some users are using Excel online and trying not to incorporate macros.

Ideas?
 

Attachments

  • Book Sample.jpg
    Book Sample.jpg
    37 KB · Views: 15

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
I found what seems to work. : =IF(E5,IF(F5="",NOW(),F5),"") Where as E5 is the Boolean Value and F5 is the stamp. This only works if iterative calculations is enabled.

It's not as clean as I'd like and not using the NOW function as intended, but it's functional! You click the check box and populates a static time/date stamp.
 

Attachments

  • Book Sample 2.jpg
    Book Sample 2.jpg
    49.8 KB · Views: 13
Upvote 0
Solution
Have you tried that with xl online? As I'm not sure that iterative calculations exist in the online version.
 
Upvote 0
@Fluff, your right, Check boxes don't work on XL online.. doh, I overlooked that. Confirmed the formula does works online, I'll have to incorporate a different mechanism other than a check box.
 
Upvote 0
You may just have to put an "X" (or something similar) into the cell instead of the checkbox, or just get users to enter the date.
 
Upvote 0
That's right on track of what I was thinking. trigger a stamp when a cell is changed. Thinking of this: =IF(A2<>"",IF(B2="",NOW(),B2),"")
 
Upvote 0
Why not just get them to enter the date?
 
Upvote 0
The more work you give the users, the less likely it's going to happen.... lol
 
Upvote 0
They can easily enter today's date using Ctrl + semi-colon
I can't see how that's any more work than entering an X (or similar) into a different cell
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,569
Latest member
Honeymonster123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top