Thank you very much! I appreciate you spreading the word about my Excel Video Lessons. I just started posting a series of lessons on how to use Dates and Times in Excel. Let me know what you think.
I maybe doing something wrong with your suggestion with the special paste function however when I enter a general number value in R11 the date and time will hold the value (meaning it will not update in cell D11) which is the start date and time.
But when I enter a general number value in S11 both D11 & E11 date and time become current and continue to update.
If you have another suggestion would love to hear it, I was thinking a macro/VBA?
If this is a procedure that you do frequently, then, yes, a Macro will speed this up considerably. Any cell that contains the function =NOW() will automatically update. Any cell that links to or refers to a cell with =NOW() in a formula will automatically update.
Use NOW() in the cells where you need to "capture" the current time. However, if you want the "output" cells to remain static (frozen) you need to copy and paste special as "values." That is a Macro project.
Really like your videos, I have learned from them.
The formula I have in place now: in cell D11, =IF(ISNUMBER(R11),NOW(),"")
Next formula is in cell E11, =IF(ISNUMBER(S11),NOW(),"")
Then in column "F" I would like to calculate the difference between D11 & E11 once both cells R11 & S11 has been completed with a general number value.
However both cells D11 & E11 (formula's) keeps the running time, I need the clock to stop once I enter a value in R11 or S11 so in F11 I can calculate D11 & E11.
1) =NOW() is a "Volatile" Function - which means that it will "update" to return the current date and time. To see this in action, enter =NOW() and then press the F9 key.
2) What I suggest is that you Copy your cells that contain - or reference - =NOW() and then, use Paste Special - Values to replace those cells that contain the formula with the resulting value.
To calculate the amount of time that has elapsed, use this formula: Ending Time - Starting Time. So, in your example, 7:00 PM (Ending Time) - 3:45 PM (Starting Time) is 3:15 - Three Hours and 15 minutes. Be sure to format the cell for your result as "h:mm"
wait..it's hard because i have excel on my macbook and never used it.. how would i calculate the time from ...lets say 3:45 - 7pm ? is that like 4 hours and 15 mins? so like...4.2? if i want to add all the hours together? :[
I was planning to do a video on counting TIME in Excel this week. Let me try to incorporate your request into the lesson. I will e-mail you when it is posted.
I coach a cross country team. I would like to do time calculations for a 3 mile race. The way we like our results to show, would be, 1 mile - split - 2 mile - split - 3 mile - pace. Example: It would look like this. 5:005:0010:005:0015:00 pace 5:00. Could you please show me how to do this. I have 2003 excel and new to it all. Thanks.
Comment removed
dancepanther 1 month ago
Comment removed
dancepanther 1 month ago
Comment removed
dancepanther 1 month ago
@dancepanther
Hello -
For some reason, your comments are marked as "Spam" and I cannot find a way to Mark them as NOT Spam.
I do need to gather more information from you before I can help you with your Time Difference Calculations.
Please just send me a short message so that I know how to contact you.
Danny Rocks
The Company Rocks
DannyRocksExcels 1 month ago
Comment removed
dancepanther 1 month ago
Will do
2009jaxs 1 year ago
Thank you, I will keep referring to your excel instruction, there good.
2009jaxs 1 year ago
@2009jaxs
Thank you very much! I appreciate you spreading the word about my Excel Video Lessons. I just started posting a series of lessons on how to use Dates and Times in Excel. Let me know what you think.
Danny Rocks
The Company Rocks
DannyRocksExcels 1 year ago
I maybe doing something wrong with your suggestion with the special paste function however when I enter a general number value in R11 the date and time will hold the value (meaning it will not update in cell D11) which is the start date and time.
But when I enter a general number value in S11 both D11 & E11 date and time become current and continue to update.
If you have another suggestion would love to hear it, I was thinking a macro/VBA?
2009jaxs 1 year ago
@2009jaxs
If this is a procedure that you do frequently, then, yes, a Macro will speed this up considerably. Any cell that contains the function =NOW() will automatically update. Any cell that links to or refers to a cell with =NOW() in a formula will automatically update.
Use NOW() in the cells where you need to "capture" the current time. However, if you want the "output" cells to remain static (frozen) you need to copy and paste special as "values." That is a Macro project.
DannyRocksExcels 1 year ago
Really like your videos, I have learned from them.
The formula I have in place now: in cell D11, =IF(ISNUMBER(R11),NOW(),"")
Next formula is in cell E11, =IF(ISNUMBER(S11),NOW(),"")
Then in column "F" I would like to calculate the difference between D11 & E11 once both cells R11 & S11 has been completed with a general number value.
However both cells D11 & E11 (formula's) keeps the running time, I need the clock to stop once I enter a value in R11 or S11 so in F11 I can calculate D11 & E11.
2009jaxs 1 year ago
@2009jaxs
Let me see if I can help you.
1) =NOW() is a "Volatile" Function - which means that it will "update" to return the current date and time. To see this in action, enter =NOW() and then press the F9 key.
2) What I suggest is that you Copy your cells that contain - or reference - =NOW() and then, use Paste Special - Values to replace those cells that contain the formula with the resulting value.
DannyRocksExcels 1 year ago
To calculate the amount of time that has elapsed, use this formula: Ending Time - Starting Time. So, in your example, 7:00 PM (Ending Time) - 3:45 PM (Starting Time) is 3:15 - Three Hours and 15 minutes. Be sure to format the cell for your result as "h:mm"
There you go!
Danny Rocks
The Company Rocks
DannyRocksExcels 1 year ago
wait..it's hard because i have excel on my macbook and never used it.. how would i calculate the time from ...lets say 3:45 - 7pm ? is that like 4 hours and 15 mins? so like...4.2? if i want to add all the hours together? :[
zandieice012 1 year ago
Hi -
I was planning to do a video on counting TIME in Excel this week. Let me try to incorporate your request into the lesson. I will e-mail you when it is posted.
Danny Rocks
The Company Rocks
DannyRocksExcels 1 year ago
I coach a cross country team. I would like to do time calculations for a 3 mile race. The way we like our results to show, would be, 1 mile - split - 2 mile - split - 3 mile - pace. Example: It would look like this. 5:00 5:00 10:00 5:00 15:00 pace 5:00. Could you please show me how to do this. I have 2003 excel and new to it all. Thanks.
terryl26 1 year ago
u r awsome
kamikim1 1 year ago
@kamikim1
Thank you for writing. It is my pleasure to post these videos. I am pleased that you enjoy them.
Danny Rocks
The Company Rocks
DannyRocksExcels 1 year ago
thank you Mr Rocks
boflex03 2 years ago
Thank you so much!
very good video.
338win 2 years ago
Thank you so much for posting this!
Spghttflm 3 years ago
It is my pleasure. I'm pleased that it you found it helpful.
Danny Rocks
DannyRocksExcels 3 years ago
very good explanation, a bit less zooming in and out would be easier on the eyes.
Thanks for posting this video.
Muuip 3 years ago
I am glad that this video helped you. Thank you for your feedback on the Zoom in feature - I appreciate you taking the time to write.
Danny Rocks
DannyRocksExcels 3 years ago
This has been flagged as spam show
18/F/Texas. hrmMMmmm. someone msg me im bored!! L
Bubbleboii 3 years ago