If checkbox = true, current date but..

[PatrickCars2]

Hi everyone.

So I'm trying to do the following.
I have a column with a Checkbox, where the options are true or false, simple.
If the option is set to TRUE, I want it to register the date that the checkbox was checked. If it has not been checked, to count the days that have passed since the estimated date.

This is my expected result being today the 27/07/2017

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Estimated Date(data)[/TD]
[TD]Checkbox[/TD]
[TD]checkbox result[/TD]
[TD]Done when?[/TD]
[TD]scenario number#[/TD]
[/TR]
[TR]
[TD]18/07/2017[/TD]
[TD]checked[/TD]
[TD]TRUE[/TD]
[TD]18/07/2017[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]20/07/2017[/TD]
[TD][/TD]
[TD]FALSE[/TD]
[TD]7[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]23/07/2017[/TD]
[TD]checked[/TD]
[TD]TRUE[/TD]
[TD]24/07/2017[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]25/07/2017[/TD]
[TD][/TD]
[TD]FALSE[/TD]
[TD]2[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]

Scenario 1: Due date 18/07/2017 and was done that day.
Scenario 2 and 4: Nobody has done it yet, so its counting the amount of days that have passed since the estimated date.
Scneario 3: Due date 23/07/2017 and was done the following day.

I tried IF(A2=TRUE,TODAY(),A2-TODAY()) but it doesn't give me the result that I want. It either gives me the current date which seems to update every time I open the Excel or when it's FALSE, the result is always 01/01/1900.

PLUS ADDON: If there is a possibility to also save in a new column the windows user who did it, the better.

Thank you in advance.
Regards.

 

[Beyond_avarice]

You can't use "=Now()" as that will just change every time you open the workbook. You would have to copy and paste value.

You could use VBA with ActiveX Checkbox:

Private Sub CheckBox1_Click()

If CheckBox1.Value = True Then
    Sheets("Sheet1").Range("D1").Value = Date
    Sheets("Sheet1").Range("G1").Value =Environ("Username")     'This will give the System Username

End If

End Sub

 

[PatrickCars2]

You can't use "=Now()" as that will just change every time you open the workbook. You would have to copy and paste value.

You could use VBA with ActiveX Checkbox:

Private Sub CheckBox1_Click()

If CheckBox1.Value = True Then
    Sheets("Sheet1").Range("D1").Value = Date
    Sheets("Sheet1").Range("G1").Value =Environ("Username")     'This will give the System Username

End If

End Sub

Thank you for the reply but.. I have never used a macro.
How do I insert it? I know you can open the VBA with alt+f11..

Thank you.
Regards.

 

[Beyond_avarice]

If you already have your "Developer" tab available; you're off to a good start.

1. Within the Developer tab, go to the "Controls" group and select "Insert". A drop down of various "Form Controls" will appear.
2. Select the "ActiveX Controls" -Checkbox
3. Place your Checkbox's on your sheet.
4. Right Click on each Checkbox and select "View Code".
5. Within the "Private Sub Checkbox(#)_Click()" and "End Sub"; Insert the code I previously posted.
6. Manipulate the Range(" ").value to reflect where you want the output to appear on your Worksheet.
7. Repeat with all Checkbox forms.
8. Set the Cells on your form, that will receive the output from the VBA; as Protected cells.

If someone at a later date, unchecks the box and rechecks it; the value will reflect the moment it is rechecked and who did it.

There are ways to prevent that, but that might end up being more complications than you are ready for.