Help with Time Calc

[Maria Celeste Taylor]

I am a newbie when it comes to excel.I can usually figure out what I need but this one has stumped me. I am not even sure this is possible. Any help would be gratefully appreciated. I am trying to make a conditional input ("y") button to enter an automatic start time =IF(A2="y",NOW(),"") Then I need to manually input a required run time in hh,mm,ss but not be a time of day. Just add the number of hours to the time of day. Then I have a required cool down time before I can run again =IF(A2="y",B2+E2+TIME(8,0,0),"") which is the start time+run time+8 hours and that needs to be compared to current time NOW() and if the total run time+cool down time >=NOW() show text "yes","NO"
Now here is my biggest problem.. I have to have multiple lines but every time I put a "y" in to start, it refreshes all NOW() functions. Once I have a start time is it possible to not refresh it again? And how do I make an input box that will just add hours to time of day and not add time of day to time of day.. I have hidden columns that are the decimal times and all my formulas refer to the hidden files for data. Would it be possible to add a real time clock display that would not refresh all NOW() functions also? I have been working on this for a week and I have myself so confused now I don't know what to do anymore. I have a sample sheet I can send someone if it would help.. Thank you so much for your help!!




EggHeadCafe - Software Developer Portal of Choice
Crystal Report And Parameter Passing Using Stored Procedure
http://www.eggheadcafe.com/tutorial...c-569d6ea46488/crystal-report-and-parame.aspx

 

[Bernie Deitrick]

Maria,

What you want to do requires having a date/time value rather than the NOW()
function. So, you can either

1) manually enter the date/time into the cell where you currently have your
first formula - press CTRL + ; (inserts the date) then a SPACE, then CTRL +
SHIFT + ; (inserts the time) and then press enter.

OR

2) use the worksheet's change event: Copy the code below, right-click the
sheet tab, select "View Code" and paste the code into the window that
appears. The code will enter the date/time as a value in column B on the
same row where you enter a y into column A (one cell at a time)

HTH,
Bernie
MS Excel MVP


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
If UCase(Target.Value) <> "Y" Then Exit Sub
Application.EnableEvents = False
Target.Offset(0, 1).Value = Now
Application.EnableEvents = True
End Sub


in message
news:[email protected]...