1

I am currently looking for a little help with Google Sheets.

I currently have three columns, B 'DATE' - C 'TIME' - D 'Dropdown list - BUY, Sell, Proforma'

So far, I have managed to do what I needed, but I am stuck on the last bit.

I have B set, that if D has any text, it enters the current date for me - =IF(ISTEXT(D56); NOW(); "")

I have C set, that if D has any text, it shows the current time - =IF(ISTEXT(D56); NOW(); "hh;mm")

My problem is this, if I leave column D empty, column C shows 'hh:mm' literally, the letters. If column D is left blank, how can I get column C to remain blank?

Improve this question
4
  • Please don't include meta-commentary in posts. They could be added in comments, but only add them if they are really necessary. The user card shows "New contributor" for new users. In your case, this label has a tooltip showing "Steve is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct." (links not included).
    – Rubén ♦
    Oct 11 at 17:46
  • The title should summarize the question; the more specific it is, the better. Avoid including tags in question titles; we have a specific feature to handle question tagging. Tags should be selected based in what is being asked, not on the question content. In this case, the question is not asking how to handle timestamps, but how to write a formula.
    – Rubén ♦
    Oct 11 at 17:51
  • @Steve if you have additional clarification or details you should edit your question to improve it not put them in comments. Comments are not permanent and your question should include all the information necessary to answer it. Please see How to Ask for more info.
    – Blindspots ♦
    Oct 11 at 21:29
  • Hi Ruben, thanks for that. I think I am a little out of my depth on here. Meta Commentary, writing scripts etc...Thank you anyway.
    – Steve
    Oct 13 at 7:28

1 Answer 1

Reset to default
1

=IF(ISTEXT(D56); NOW(); "hh;mm")

That formula will always show either the current time or the text string "hh;mm". To show a blank instead, use this:

=if(istext(D56); now(); iferror(ø))

To show the time as hours and minutes, format the column as Format > Number > Custom number format > h:mm.

It is unclear why you would need to show the current time on every row. To get permanent timestamps that stay put instead of always showing the current time, use a script such as yetAnotherTimestamp_. For more options, search this site for timestamp [google-sheets].

Improve this answer
2
  • Hi doubleunary. Thank you for your reply. I have just tried the one you suggested and I think I haven't explained very well. When I click the dropdown and select BUY;SELL,PROFORMA, my first column shows the date. I would like the next to show the time I seclected BUY,SELL etc and remain at that time. I have just noticed the one I have keeps updating...Duh!
    – Steve
    Oct 11 at 15:35
  • To get permanent timestamps, use a script. Ask only one question per post. See What should I do when someone answers my question?
    – doubleunary
    Oct 11 at 16:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.