Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting up a date and time stamp in a cell
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting up a date and time stamp in a cell
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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting up a date and time stamp in a cell
Dave,
Just to be more clear, I want to convert minutes to days and hours,minutes,seconds. For example: Minute value in colume C is 44794.17. In col D the result is 31 --- 02:34:10 - saying 31 days 2hours, 34min,10sec. The next entery in col C is 46163.17. In col D the result I would like is 31 --- 01:23:10. 32 days,1hour,23min,10sec. And so on, increase the day by one every 24 hours. Sorry for the confussion between second and minutes. Thanks, 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 . . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting up a date and time stamp in a cell
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting up a date and time stamp in a cell
Dave,
Thank you very much for your help. The formula you provided does exactly what I want. Alex -----Original Message----- 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 news:010501c338ef$3e9b2500 ... 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Time date stamp | Excel Worksheet Functions | |||
Date-time stamp | Excel Discussion (Misc queries) | |||
how do I put a date/time stamp in comments? | Excel Discussion (Misc queries) | |||
Date-Time Stamp | Excel Discussion (Misc queries) | |||
date/time stamp | Excel Worksheet Functions |