Excel: Keep cell containing date the same after being set, without macros
up vote
3
down vote
favorite
I have a table that looks like this:
What I want to achieve is that when you fill in a row (other than the 'Time' section), the 'Time' column automatically fills itself in. Then, it stays the same forever, regardless of how the table is altered. What I have now is:
=IF(E3="", IF(COUNTBLANK(A2:D2)=0, NOW(), ""), E2)
This works to some extent, in that it sets the date when you finish filling in a row, and the date does not change; however, on the last row of the table the date will still change, until a new last row is created. However, I need the dates to stay exactly the same as right after their row is filled in.
I've tried adding helper columns and SUMPRODUCT to track what to update and when, and what to keep the same, but nothing worked; therefore, I reverted to the above formula that mostly works, other than on the last row (as aforementioned).
Please let me know if you have any ideas. As the title suggests, I cannot use macros.
microsoft-excel worksheet-function microsoft-excel-2016 date
asked Nov 24 at 14:26
Geza Kerecsenyi
799
add a comment |
up vote
3
down vote
favorite
I have a table that looks like this:
What I want to achieve is that when you fill in a row (other than the 'Time' section), the 'Time' column automatically fills itself in. Then, it stays the same forever, regardless of how the table is altered. What I have now is:
=IF(E3="", IF(COUNTBLANK(A2:D2)=0, NOW(), ""), E2)
This works to some extent, in that it sets the date when you finish filling in a row, and the date does not change; however, on the last row of the table the date will still change, until a new last row is created. However, I need the dates to stay exactly the same as right after their row is filled in.
I've tried adding helper columns and SUMPRODUCT to track what to update and when, and what to keep the same, but nothing worked; therefore, I reverted to the above formula that mostly works, other than on the last row (as aforementioned).
Please let me know if you have any ideas. As the title suggests, I cannot use macros.
microsoft-excel worksheet-function microsoft-excel-2016 date
asked Nov 24 at 14:26
Geza Kerecsenyi
799
I think you are going to be limited to choosing between entering the date/time manually, or using VBA.
– Ron Rosenfeld
Nov 26 at 1:40
If you don't want to use macro to get the result, try to use shortcut key enter time manually: ctrl+shift+；
– Lee
Nov 26 at 8:45
add a comment |
up vote
3
down vote
favorite
up vote
3
down vote
favorite
I have a table that looks like this:
What I want to achieve is that when you fill in a row (other than the 'Time' section), the 'Time' column automatically fills itself in. Then, it stays the same forever, regardless of how the table is altered. What I have now is:
=IF(E3="", IF(COUNTBLANK(A2:D2)=0, NOW(), ""), E2)
This works to some extent, in that it sets the date when you finish filling in a row, and the date does not change; however, on the last row of the table the date will still change, until a new last row is created. However, I need the dates to stay exactly the same as right after their row is filled in.
I've tried adding helper columns and SUMPRODUCT to track what to update and when, and what to keep the same, but nothing worked; therefore, I reverted to the above formula that mostly works, other than on the last row (as aforementioned).
Please let me know if you have any ideas. As the title suggests, I cannot use macros.
microsoft-excel worksheet-function microsoft-excel-2016 date
asked Nov 24 at 14:26
Geza Kerecsenyi
799
I have a table that looks like this:
What I want to achieve is that when you fill in a row (other than the 'Time' section), the 'Time' column automatically fills itself in. Then, it stays the same forever, regardless of how the table is altered. What I have now is:
=IF(E3="", IF(COUNTBLANK(A2:D2)=0, NOW(), ""), E2)
This works to some extent, in that it sets the date when you finish filling in a row, and the date does not change; however, on the last row of the table the date will still change, until a new last row is created. However, I need the dates to stay exactly the same as right after their row is filled in.
I've tried adding helper columns and SUMPRODUCT to track what to update and when, and what to keep the same, but nothing worked; therefore, I reverted to the above formula that mostly works, other than on the last row (as aforementioned).
Please let me know if you have any ideas. As the title suggests, I cannot use macros.
microsoft-excel worksheet-function microsoft-excel-2016 date
microsoft-excel worksheet-function microsoft-excel-2016 date
asked Nov 24 at 14:26
Geza Kerecsenyi
799
asked Nov 24 at 14:26
Geza Kerecsenyi
799
edited Dec 2 at 10:07
asked Nov 24 at 14:26
Geza Kerecsenyi
799
asked Nov 24 at 14:26
Geza Kerecsenyi
799
asked Nov 24 at 14:26
Geza Kerecsenyi
799
799
I think you are going to be limited to choosing between entering the date/time manually, or using VBA.
– Ron Rosenfeld
Nov 26 at 1:40
If you don't want to use macro to get the result, try to use shortcut key enter time manually: ctrl+shift+；
– Lee
Nov 26 at 8:45
add a comment |
I think you are going to be limited to choosing between entering the date/time manually, or using VBA.
– Ron Rosenfeld
Nov 26 at 1:40
If you don't want to use macro to get the result, try to use shortcut key enter time manually: ctrl+shift+；
– Lee
Nov 26 at 8:45
I think you are going to be limited to choosing between entering the date/time manually, or using VBA.
– Ron Rosenfeld
Nov 26 at 1:40
I think you are going to be limited to choosing between entering the date/time manually, or using VBA.
– Ron Rosenfeld
Nov 26 at 1:40
If you don't want to use macro to get the result, try to use shortcut key enter time manually: ctrl+shift+；
– Lee
Nov 26 at 8:45
If you don't want to use macro to get the result, try to use shortcut key enter time manually: ctrl+shift+；
– Lee
Nov 26 at 8:45
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
accepted
Since you have restricted the scope of the solution to Non-VBA in that case I would like to suggest a little tricky solution.
How it works:
- Before you enter the Formula Click,,
File, Option then Formula and Check the Enable Iterative Calculation and set Maximum iteration Value to 1000.- Finish with Ok
- Set Appropriate Time Format for requited Data Range.
Write this Formula in Cell E1 and Fill down.
=IF(COUNTBLANK(A1:D1)<>0,"",IF(E1="",NOW(),E1))
Note, the Time will remain unchanged unless you will not delete any value and renter new one in cell/cells. (Time will remain unchanged in case of modification).
answered Dec 1 at 9:34
Rajesh S
3,5401422
I added a helper column with=IF(COUNTBLANK(A1:D1)=0, TRUE, IF(F1="", FALSE, F1)), and that handles deletion of values as well.
– Geza Kerecsenyi
Dec 2 at 10:18
@GezaKerecsenyi,, glad to help you keep asking ☺
– Rajesh S
Dec 3 at 4:58
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
accepted
Since you have restricted the scope of the solution to Non-VBA in that case I would like to suggest a little tricky solution.
How it works:
- Before you enter the Formula Click,,
File, Option then Formula and Check the Enable Iterative Calculation and set Maximum iteration Value to 1000.- Finish with Ok
- Set Appropriate Time Format for requited Data Range.
Write this Formula in Cell E1 and Fill down.
=IF(COUNTBLANK(A1:D1)<>0,"",IF(E1="",NOW(),E1))
Note, the Time will remain unchanged unless you will not delete any value and renter new one in cell/cells. (Time will remain unchanged in case of modification).
answered Dec 1 at 9:34
Rajesh S
3,5401422
I added a helper column with=IF(COUNTBLANK(A1:D1)=0, TRUE, IF(F1="", FALSE, F1)), and that handles deletion of values as well.
– Geza Kerecsenyi
Dec 2 at 10:18
@GezaKerecsenyi,, glad to help you keep asking ☺
– Rajesh S
Dec 3 at 4:58
add a comment |
up vote
0
down vote
accepted
Since you have restricted the scope of the solution to Non-VBA in that case I would like to suggest a little tricky solution.
How it works:
- Before you enter the Formula Click,,
File, Option then Formula and Check the Enable Iterative Calculation and set Maximum iteration Value to 1000.- Finish with Ok
- Set Appropriate Time Format for requited Data Range.
Write this Formula in Cell E1 and Fill down.
=IF(COUNTBLANK(A1:D1)<>0,"",IF(E1="",NOW(),E1))
Note, the Time will remain unchanged unless you will not delete any value and renter new one in cell/cells. (Time will remain unchanged in case of modification).
answered Dec 1 at 9:34
Rajesh S
3,5401422
I added a helper column with=IF(COUNTBLANK(A1:D1)=0, TRUE, IF(F1="", FALSE, F1)), and that handles deletion of values as well.
– Geza Kerecsenyi
Dec 2 at 10:18
@GezaKerecsenyi,, glad to help you keep asking ☺
– Rajesh S
Dec 3 at 4:58
add a comment |
up vote
0
down vote
accepted
up vote
0
down vote
accepted
Since you have restricted the scope of the solution to Non-VBA in that case I would like to suggest a little tricky solution.
How it works:
- Before you enter the Formula Click,,
File, Option then Formula and Check the Enable Iterative Calculation and set Maximum iteration Value to 1000.- Finish with Ok
- Set Appropriate Time Format for requited Data Range.
Write this Formula in Cell E1 and Fill down.
=IF(COUNTBLANK(A1:D1)<>0,"",IF(E1="",NOW(),E1))
Note, the Time will remain unchanged unless you will not delete any value and renter new one in cell/cells. (Time will remain unchanged in case of modification).
answered Dec 1 at 9:34
Rajesh S
3,5401422
Since you have restricted the scope of the solution to Non-VBA in that case I would like to suggest a little tricky solution.
How it works:
- Before you enter the Formula Click,,
File, Option then Formula and Check the Enable Iterative Calculation and set Maximum iteration Value to 1000.- Finish with Ok
- Set Appropriate Time Format for requited Data Range.
Write this Formula in Cell E1 and Fill down.
=IF(COUNTBLANK(A1:D1)<>0,"",IF(E1="",NOW(),E1))
Note, the Time will remain unchanged unless you will not delete any value and renter new one in cell/cells. (Time will remain unchanged in case of modification).
answered Dec 1 at 9:34
Rajesh S
3,5401422
answered Dec 1 at 9:34
Rajesh S
3,5401422
answered Dec 1 at 9:34
Rajesh S
3,5401422
answered Dec 1 at 9:34
Rajesh S
3,5401422
3,5401422
I added a helper column with=IF(COUNTBLANK(A1:D1)=0, TRUE, IF(F1="", FALSE, F1)), and that handles deletion of values as well.
– Geza Kerecsenyi
Dec 2 at 10:18
@GezaKerecsenyi,, glad to help you keep asking ☺
– Rajesh S
Dec 3 at 4:58
add a comment |
I added a helper column with=IF(COUNTBLANK(A1:D1)=0, TRUE, IF(F1="", FALSE, F1)), and that handles deletion of values as well.
– Geza Kerecsenyi
Dec 2 at 10:18
@GezaKerecsenyi,, glad to help you keep asking ☺
– Rajesh S
Dec 3 at 4:58
I added a helper column with
=IF(COUNTBLANK(A1:D1)=0, TRUE, IF(F1="", FALSE, F1)), and that handles deletion of values as well.– Geza Kerecsenyi
Dec 2 at 10:18
I added a helper column with
=IF(COUNTBLANK(A1:D1)=0, TRUE, IF(F1="", FALSE, F1)), and that handles deletion of values as well.– Geza Kerecsenyi
Dec 2 at 10:18
@GezaKerecsenyi,, glad to help you keep asking ☺
– Rajesh S
Dec 3 at 4:58
@GezaKerecsenyi,, glad to help you keep asking ☺
– Rajesh S
Dec 3 at 4:58
add a comment |
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1378027%2fexcel-keep-cell-containing-date-the-same-after-being-set-without-macros%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
I think you are going to be limited to choosing between entering the date/time manually, or using VBA.
– Ron Rosenfeld
Nov 26 at 1:40
If you don't want to use macro to get the result, try to use shortcut key enter time manually: ctrl+shift+；
– Lee
Nov 26 at 8:45