How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask The-PC-Guy Your Own Question

The-PC-Guy
The-PC-Guy, Computer Manager
Category: Microsoft Office
Satisfied Customers: 1896
Experience:  20 years experience providing remote computer support
62934938
Type Your Microsoft Office Question Here...
The-PC-Guy is online now
A new question is answered every 9 seconds

I need to autofill a cell (E2) in Excel with the date/time

Customer Question

I need to autofill a cell (E2) in Excel with the date/time that the line is marked as complete (i.e. in cell F2 it goes from blank to "Yes" from a datalist). I need to also fix that date so it does change with the actual time and remains a true indication of completion time. Separate to that I am doing a duration formula to calcualte the time taken from the start -I can do that bit as it's simple but wondered if the intial part is possible?
Submitted: 1 year ago.
Category: Microsoft Office
Expert:  The-PC-Guy replied 1 year ago.

you can use

=IF(F2="Yes",TODAY(),"")

note: this will change the date in the cell to whatever the current date is, even if you open the sheet a few days later.

if you want the date to remain unchanged if you open the sheet later, then you will need some VBA which I can write for you if you would like.

---------------------------------------------------------------------------------------------------------------

let me know if you have any questions, problems, or concerns

PLEASE DON'T FORGET TO RATE SO I AM PAID FOR MY TIME

IT WILL NOT COST ANYTHING ADDITIONAL BEYOND THE VALUE OF YOUR QUESTION

TO RATE, CLICK THE STARS AT THE TOP OF YOUR SCREEN

Do not rate negatively, instead continue the conversation with me so I can address any of your concerns

---------------------------------------------------------------------------------------------------------------

if you have any questions in the future you can visit my profile

http://www.justanswer.com/computer/expert-1expert-2rulethemall/

and ask a question right in my box,

also you can put "PC Guy only" in your question title if you want to get to me.

Expert:  The-PC-Guy replied 1 year ago.

see answer

Customer: replied 1 year ago.

Thanks sorry yes got that far but needed the time too and it to be fixed that's the part I'm struggling with -looks like the reason why. How complex would the VBA be?

Expert:  The-PC-Guy replied 1 year ago.

the date/time is

=IF(F2="Yes",NOW(),"")

VBA would not be too complex, the question is do you need the date to remain the same even if the sheet is opened later?

Customer: replied 1 year ago.

Hi - yes, date to remain the same if sheet opened later but potentially could manually amend (albeit not likely) just want it to have a static record of the time taken which won't work if using the above

Expert:  The-PC-Guy replied 1 year ago.

ok, then in this case you will need VBA,

I will have to charge a little bit more to do it because it is somewhat technical, but not overly complex.

Are you going to do this on more than one cell, inotherwords have a range of cells, and when one of them changes, put the appropriate timestamp in?

Customer: replied 1 year ago.

okay if you could give me a quote please.

It will only effect one cell at a time i.e. confirm Yes in F2 and only change D2 or similar. However, I will have multiple tabs working separately, same layout and principle so I'd need to mirror on the other tabs. Attached is my template so far - very basic

Attachment: 2015-11-04_152245_project_tracker.xlsx

Expert:  The-PC-Guy replied 1 year ago.

so before I give a quote let me find out exactly what you want to do. I assume that someone will manually change a value in column C, and that will then populate column E with the current time, and column F with a formula that is the difference between the 2 times or does someone enter something in column G to tirgger column E and F?

Customer: replied 1 year ago.

Apologies, was in a meeting First step would be entering "Yes" in column C which would populate column D with start date/time (and fix); then upon completion do the same in column G which will populate column E and then the formula in column F would calculate the time taken - that bit works okay; albeit it would be useful to know if it's possible to build in some workings that exclude certain hours i.e. just include working hours; although think I'm getting a bit carried away now! I need to go out shortly so may not be able to get back to you until tomorrow but hopefully the above gives you the detail you need. Thank you

Expert:  The-PC-Guy replied 1 year ago.

i think it would be around $99 to add the basic functionality.

If you want to start getting into calculating only certain hours, then a more complicated template would probably be required with some way of indicating whether it is a holiday or working day, or weekend, and how many hours were working/ weekend, ect.. Of course that would increase the cost.

If you would like to have me do just the basic, and then you use it for a while and see if you need more, you could always hire me again in the future to do more functionality.

I am sending the additional service offer now. I will check back tomarrow with you as requested.

Expert:  The-PC-Guy replied 1 year ago.

are you still interested in doing this?

Expert:  The-PC-Guy replied 1 year ago.

haven't heard anything from you.

are you still interested in doing this?

Related Microsoft Office Questions