Timestamp in excel using drop-downs

Question

Timestamp in excel using drop-downs

Shani Cook 20

Hello, I am trying to create a timestamp without using VA, or whatever it is called-sorry I can't remember. What I'm working with is a IF formula but I'm not sure if I'm doing it correctly or filling in the proper information. What I have is =IF(AND(A17<>"D",A17<>A17),NOW(),IF(A17<>"D",D17,"TIME")). But I don't feel that's right. What I need is if the drop-down is D than the column titled end time would have the exact timestamp when D is selected. So if A17 is D than D17 needs to be the time. Thank you so much if anyone is able to help.

0 comments

Answer accepted by question author

0 additional answers

Your answer

Answer 1

David Sanchez 19,075 Independent Advisor

Hi there! My name is David. It’s a pleasure to help you today.Regarding your question, I can suggest the following formula:

=IF($A17="D", IF(D17="", NOW(), D17), "")

This formula will display the exact timestamp when "D" is selected from the drop-down list, and will show a blank cell if any other option is chosen.

However, for it to work correctly, you'll need to adjust your Excel settings. Go to File > Options > Formulas, check the box for Enable iterative calculation, then set Maximum Iterations = 1 and Maximum Change = 0.1, and click OK.

Finally, in the cell where you apply the formula, change the format from General to Time. If you want to display both the date and time, select More Number Formats, then choose Custom, and from the list select m/d/yyyy hh:mm.

Let me know if the shared information has been helpful. Regards.

Shani Cook 20 Reputation points

2025-10-30T23:51:50.3166667+00:00

This was very helpful! I was able to do as you recommended. The only thing I ran into when testing was that it was not inputting the current time, but rather hours different. I'm not sure how to fix that.
Shani Cook 20 Reputation points

2025-10-31T00:13:34.97+00:00

This was helpfully. I was able to do as you said, but the time is off its inputting 3 hours in the past
David Sanchez 19,075 Reputation points Independent Advisor

2025-10-31T01:32:00.98+00:00
Hi! Thanks for staying in touch, I'm glad to hear the formula I shared was helpful. Now, regarding the three-hour difference, this may be due to your time zone settings.

If you're using Windows, you can adjust your time zone by going to Start > Settings > Time & Language > Date & Time. There, you can enable automatic time settings or manually select the correct time zone for your country and region. After that, click Sync now to update the time.

If you're using macOS, go to System Settings > General > Date & Time. As with Windows, you can either set the time zone automatically or choose it manually.

Alternatively, if you prefer a more direct adjustment within the formula itself, you can apply the following:

=IF($A17="D", IF(D17="", NOW()+TIME(3,0,0), D17), "")

This will apply a 3-hour offset in the formula's calculation.

Let me know if the shared information has been helpful. Regards.
Shani Cook 20 Reputation points

2025-10-31T14:14:41.65+00:00

Thank you!!! I tried the first, but adjusting the formula worked! I appreciate your help!
David Sanchez 19,075 Reputation points Independent Advisor

2025-10-31T15:56:30.93+00:00

Hi! I'm really glad to hear that the formula adjustment worked and the time difference issue has been resolved.

If you have any other questions related to this post, feel free to let me know. Also, don’t forget you can mark the response as helpful so that other users with similar questions can find it more easily.

And remember, if you have a different question, you’re always welcome to create a new post on Microsoft Q&A to receive help. Best regards!
Shani Cook 20 Reputation points

2025-11-06T14:47:35.14+00:00

Hi David, I'm running into a sloght issue. The time stamp works, but there are times where it randomly change to a completely different number and I'm not sure what is happening. Or how to get the actual data back.

Share via

Timestamp in excel using drop-downs

0 additional answers

Your answer