First a helper cell is just a cell that holds a formula. In this case, a
formula like:
A1 held 44794.17
B1 held this formula:
=INT(A1/1440)&":"&HOUR(A1/1440)&":"&MINUTE(A1/1440)&":"&SECOND(A1/1440)
But it evaluated to:
31:2:34:10
(The original formula based on seconds had the same problem with leading 0's.)
This might get you closer:
=INT(A1/1440)&" --- "&TEXT(HOUR(A1/1440),"00")
&":"&TEXT(MINUTE(A1/1440),"00")&":"&TEXT(SECOND(A1/1440),"00")
(all one cell)
this formula evaluated to: 31 --- 02:34:10
and with 47871.50, it evaluated to: 33 --- 05:51:30
Alex wrote:
Dave,
Sorry for any confussion. I am using minutes to calculate
to days, hours, minutes ,seconds. Not seconds.
Example: minutes = 44794.17 should be 31 --- 02:34:10
(31 days --- 02 hours:34 min:10 sec)
46163.17 converts to 01 --- 01:23:10.
47871.50 converts to 02 --- 05:51:30.
I know it is because of the date format I am using which
will not go pass 31 days. Is there a way to make
46163.17 convert to 32 --- 01:23:10.
47871.50 convert to 33 --- 05:51:30.
Could a VB code do the conversion instead of a cell
formula?
Sorry for any duplicate messages.
Thanks for your help.
Alex
-----Original Message-----
Dave,
I tried the formula and I get #VALUE in the cell that has
the formula. Cell A1 has the number of seconds. What is a
helper cell?
I really do not need the words hours,minutes,seconds as
part of the answer in the cell. All I need is 32 ---
01:23:10 for 46163.17 minutes.
Thanks
Alex
-----Original Message-----
Netscape crashed while sending (pardon any duplicate
post):
That dd represents the date of the month. So I think
you'll have to go to
another approach.
I used a helper cell and did this:
=INT(A1/86400)&":"&HOUR(A1/86400)&":"&MINUTE(A1/86400)
&":"&SECOND(A1/86400)
If A1 contained the number of seconds.
Alex wrote:
Dave,
Thank you. The delete key does exactly what I want.
I have another problem which is:
I am using the following formula to calculate the
number
of seconds to the number of days:hours:minutes:seconds
=
(c5/60)24. Cell C5 has the time is seconds. The cell is
formatted as custom dd --- hh:mm:ss. This works ok up
to
31 days. Example, 44794.17 seconds converts to
31days:02hours:23minutes:10seconds. This is what I
want.
But 46163.17 seconds convert to
01day:01hour:23minutes:10seconds. I would like it to
display 32days:01hour:minutes:10seconds and so on as
the
seconds increment.
What format should I use so that the days are limited
to
31 calendar days. I would like the day to continue to
increment as the seconds increment.
Thanks
Alex
-----Original Message-----
Don't hit the space bar when you're clearing cells in
column A. Hit the delete
key (on the keyboard) (or Edit|Clear|Contents).
Alex wrote:
Tom,
This code works fine when I enter a different value
in
colume A. However, I would like to force the time
stamp
in colume B to be blank when I enter a zero or a
space
(removing the data in columew A). With the current
code
when I enter a zero in colume A I get a time stamp
in
colume B and when I enter a "space" in colume A I
get "#VALUE!" in colume C and D.
FYI, in colume C I have the following formula =A2/6
and
in
colume D =(c2/60)/24. Colume C and D work fine.
The
time
stamp works fine. But I would like colume B to
blank
when
I remove the data from colume A or enter a zero in
colume
A.
Thanks for your help.
ALEX
-----Original Message-----
Assume you will make entries one cell at a time,
but
may
delete multiple
cells.
Private Sub Worksheet_Change(ByVal Target As
Excel.Range)
If Target.Count = 1 Then
On Error GoTo errHandler
If Target.Column = 1 Then
Application.EnableEvents = False
If Target.Value < "" Then
Target.Offset(0, 1).Value = Now
Else
Target.Offset(0, 1).ClearContents
End If
End If
Else
If Target.Columns.Count = 1 And _
Target(1).Column = 1 Then
Application.EnableEvents = False
For Each cell In Target
If cell.Value = "" Then _
Target.Offset(0, 1).ClearContents
Next
End If
End If
errHandler:
Application.EnableEvents = True
End Sub
Lightly tested.
Regards,
Tom Ogilvy
Alex wrote in message
...
Tom,
Thank you very much for your help. The code work
great! I
have one minor request. I would the time stamp
colume
(B)
to go back to a blank cell when I remove/delete
the
data
in colume A. With the current code when I
remove/delete
data from colume A, colume B still retains the
stamp
until
I enter new data in colume A and the the stamp
changes
to
the new time and date.
Thank you,
Alex
-----Original Message-----
Right click on the sheet tab and put in code
like
this:
Private Sub Worksheet_Change(ByVal Target As
Excel.Range)
If Target.Count 1 Then Exit Sub
On Error GoTo errHandler
If Target.Column = 1 Then
Application.EnableEvents = False
Target.Offset(0, 1).Value = Now
End If
errHandler:
Application.EnableEvents = True
End Sub
Regards,
Tom Ogilvy
Alex wrote in message
...
Thank you for your previous reply.
I have another problem. I want to time stamp
a
cell
entry.
Example: cell A2 thru A25 are cell enrty
fields
(any
value) in cell B2 thru B25 I want the date and
time
that
entries were made in the A colume.
I used the following in B2 thru B25 =IF
(A20,NOW
(),IF
(A2=0,"")). This works except that when I
enter
another
value in the next A cell (A3) the pervious
date
and
time
in the B colume (B2) changes to the lastest
time
entry
in
A3.
I tried using Excel help but I can't find
anything
that
discribes what I want to do.
Trying to simplfy. When I enter data in A2
date
andtime
stamp in B2 and keep that time. If ten minutes
later
I
enter data in A3 B3 should date and time the
entry.
The
entries should be 10 minutes apart.
Thank you,
Alex
.
.
--
Dave Peterson
.
--
Dave Peterson
.
.
--
Dave Peterson