Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need a formula about time stamp....?
Hi all,
For example, A1 is column for date input and B1 is for the time date is input in A1. If I want excel to automatically show time the date is input, what formula is used? Now, i wrote one formula but it doesn't really fit my requirement. I'm using like =if(a1<"",now()) what I get at frist place seems ok but I just realized that by using this formula, the time always changes. Please help ToMMie |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need a formula about time stamp....?
You need a "frozen" time:
1. FIRST click on B1 and touch CNTRL-SHFT-: 2. Then enter stuff in A1 -- Gary''s Student - gsnu200804 "ToMMie" wrote: Hi all, For example, A1 is column for date input and B1 is for the time date is input in A1. If I want excel to automatically show time the date is input, what formula is used? Now, i wrote one formula but it doesn't really fit my requirement. I'm using like =if(a1<"",now()) what I get at frist place seems ok but I just realized that by using this formula, the time always changes. Please help ToMMie |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need a formula about time stamp....?
Couple of methods on John McGimpsey's site
One without VBA and one with. http://www.mcgimpsey.com/excel/timestamp.html Gord Dibben MS Excel MVP On Thu, 11 Sep 2008 08:56:01 -0700, ToMMie wrote: Hi all, For example, A1 is column for date input and B1 is for the time date is input in A1. If I want excel to automatically show time the date is input, what formula is used? Now, i wrote one formula but it doesn't really fit my requirement. I'm using like =if(a1<"",now()) what I get at frist place seems ok but I just realized that by using this formula, the time always changes. Please help ToMMie |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need a formula about time stamp....?
Thanks a lot.
Well I'm have another few more questions. If you could help me out, it would be very kind of you. 1. to enter number in a cell, the lenght shouldn't be longer than 15 digits. but I need to key in 1111222233334444 into a cell but I need an auto format to display like 1111 2222 3333 4444. but the thing is that I format as custom like 0000 0000 0000 0000 but it shows 1111 2222 3333 4440. (problem!!) So, someone suggested me to key it as text and include spaces which is not convenient to my users. Do you know any other ways to do this? 2. I groupped 5 columns. Then, I protect the worksheet. but I just can't expand / collapse those groupped column. how can i fix that? Million thanks. ToMMie "Gord Dibben" wrote: Couple of methods on John McGimpsey's site One without VBA and one with. http://www.mcgimpsey.com/excel/timestamp.html Gord Dibben MS Excel MVP On Thu, 11 Sep 2008 08:56:01 -0700, ToMMie wrote: Hi all, For example, A1 is column for date input and B1 is for the time date is input in A1. If I want excel to automatically show time the date is input, what formula is used? Now, i wrote one formula but it doesn't really fit my requirement. I'm using like =if(a1<"",now()) what I get at frist place seems ok but I just realized that by using this formula, the time always changes. Please help ToMMie |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need a formula about time stamp....?
This stumps me as well.
If you don't get a good answer to this post, I suggest you enter a new post titled something like: Formatting Text Input -- Gary''s Student - gsnu200804 "ToMMie" wrote: Thanks a lot. Well I'm have another few more questions. If you could help me out, it would be very kind of you. 1. to enter number in a cell, the lenght shouldn't be longer than 15 digits. but I need to key in 1111222233334444 into a cell but I need an auto format to display like 1111 2222 3333 4444. but the thing is that I format as custom like 0000 0000 0000 0000 but it shows 1111 2222 3333 4440. (problem!!) So, someone suggested me to key it as text and include spaces which is not convenient to my users. Do you know any other ways to do this? 2. I groupped 5 columns. Then, I protect the worksheet. but I just can't expand / collapse those groupped column. how can i fix that? Million thanks. ToMMie "Gord Dibben" wrote: Couple of methods on John McGimpsey's site One without VBA and one with. http://www.mcgimpsey.com/excel/timestamp.html Gord Dibben MS Excel MVP On Thu, 11 Sep 2008 08:56:01 -0700, ToMMie wrote: Hi all, For example, A1 is column for date input and B1 is for the time date is input in A1. If I want excel to automatically show time the date is input, what formula is used? Now, i wrote one formula but it doesn't really fit my requirement. I'm using like =if(a1<"",now()) what I get at frist place seems ok but I just realized that by using this formula, the time always changes. Please help ToMMie |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need a formula about time stamp....?
!. You have many replies and suggestions for this at your other thread. I
can add nothing to those. 2. Enter this code in Thisworkbook module. Private Sub Workbook_Open() With Worksheets("Sheet1") .Protect Password:="justme", userinterfaceonly:=True .EnableOutlining = True '.EnableAutoFilter = True End With End Sub Note; the outliing and/or autofilter must be applied prior to saving and re-opening. Gord Dibben MS Excel MVP On Thu, 11 Sep 2008 10:01:01 -0700, ToMMie wrote: Thanks a lot. Well I'm have another few more questions. If you could help me out, it would be very kind of you. 1. to enter number in a cell, the lenght shouldn't be longer than 15 digits. but I need to key in 1111222233334444 into a cell but I need an auto format to display like 1111 2222 3333 4444. but the thing is that I format as custom like 0000 0000 0000 0000 but it shows 1111 2222 3333 4440. (problem!!) So, someone suggested me to key it as text and include spaces which is not convenient to my users. Do you know any other ways to do this? 2. I groupped 5 columns. Then, I protect the worksheet. but I just can't expand / collapse those groupped column. how can i fix that? Million thanks. ToMMie "Gord Dibben" wrote: Couple of methods on John McGimpsey's site One without VBA and one with. http://www.mcgimpsey.com/excel/timestamp.html Gord Dibben MS Excel MVP On Thu, 11 Sep 2008 08:56:01 -0700, ToMMie wrote: Hi all, For example, A1 is column for date input and B1 is for the time date is input in A1. If I want excel to automatically show time the date is input, what formula is used? Now, i wrote one formula but it doesn't really fit my requirement. I'm using like =if(a1<"",now()) what I get at frist place seems ok but I just realized that by using this formula, the time always changes. Please help ToMMie |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need a formula about time stamp....?
Hi Dibben,
Could you please help me a bit on No. 2? I'm not really sure whether or not I follow the right steps as I've never done with VB before. According to your suggestion, I do, first, group columns I want 1. go to Tools -- Macro -- Visual Basic Editor. It prompts me the windows of MS Visual Basic 2. Then I double click This Workbook module. A new windows pops up 3. At drop down menu on left top, I select Workbook 4. Next one, I select Open 5. There is a message in below pane like Private Sub Workbook_Open() End Sub 6. I copy the code and paste it in between like Private Sub Workbook_Open() With Worksheets("Sheet1") .Protect Password:="justme", userinterfaceonly:=True .EnableOutlining = True '.EnableAutoFilter = True End With End Sub 7. As I notice that font color of '.EnableAutoFilter = True turns green. I'm not sure if there is a mistake? So I decide to delete ' and then it turns black color which I think I do the right thing. 8. I protect worksheet then hit save and close 9. I close file and reopen it. 10. but I still can't do the expand or collapse the groupped columns. PLease kindly help ToMMie "Gord Dibben" wrote: !. You have many replies and suggestions for this at your other thread. I can add nothing to those. 2. Enter this code in Thisworkbook module. Private Sub Workbook_Open() With Worksheets("Sheet1") .Protect Password:="justme", userinterfaceonly:=True .EnableOutlining = True '.EnableAutoFilter = True End With End Sub Note; the outliing and/or autofilter must be applied prior to saving and re-opening. Gord Dibben MS Excel MVP On Thu, 11 Sep 2008 10:01:01 -0700, ToMMie wrote: Thanks a lot. Well I'm have another few more questions. If you could help me out, it would be very kind of you. 1. to enter number in a cell, the lenght shouldn't be longer than 15 digits. but I need to key in 1111222233334444 into a cell but I need an auto format to display like 1111 2222 3333 4444. but the thing is that I format as custom like 0000 0000 0000 0000 but it shows 1111 2222 3333 4440. (problem!!) So, someone suggested me to key it as text and include spaces which is not convenient to my users. Do you know any other ways to do this? 2. I groupped 5 columns. Then, I protect the worksheet. but I just can't expand / collapse those groupped column. how can i fix that? Million thanks. ToMMie "Gord Dibben" wrote: Couple of methods on John McGimpsey's site One without VBA and one with. http://www.mcgimpsey.com/excel/timestamp.html Gord Dibben MS Excel MVP On Thu, 11 Sep 2008 08:56:01 -0700, ToMMie wrote: Hi all, For example, A1 is column for date input and B1 is for the time date is input in A1. If I want excel to automatically show time the date is input, what formula is used? Now, i wrote one formula but it doesn't really fit my requirement. I'm using like =if(a1<"",now()) what I get at frist place seems ok but I just realized that by using this formula, the time always changes. Please help ToMMie |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need a formula about time stamp....?
You were correct in removing the ' from .EnableOutLining = True
The ' designates a non-running comment or remmed out line, in this case As far as you not being able to expand/collapse the grouped columns........the code works for me as advertised. You sure the sheet with the outlining is Sheet1? This is the only code you need in Thisworkbook. Private Sub Workbook_Open() With Worksheets("Sheet1") .Protect Password:="justme", userinterfaceonly:=True .EnableOutlining = True .EnableAutoFilter = True 'not applicable in your case, I think End With End Sub Gord On Fri, 12 Sep 2008 00:13:00 -0700, ToMMie wrote: Hi Dibben, Could you please help me a bit on No. 2? I'm not really sure whether or not I follow the right steps as I've never done with VB before. According to your suggestion, I do, first, group columns I want 1. go to Tools -- Macro -- Visual Basic Editor. It prompts me the windows of MS Visual Basic 2. Then I double click This Workbook module. A new windows pops up 3. At drop down menu on left top, I select Workbook 4. Next one, I select Open 5. There is a message in below pane like Private Sub Workbook_Open() End Sub 6. I copy the code and paste it in between like Private Sub Workbook_Open() With Worksheets("Sheet1") .Protect Password:="justme", userinterfaceonly:=True .EnableOutlining = True '.EnableAutoFilter = True End With End Sub 7. As I notice that font color of '.EnableAutoFilter = True turns green. I'm not sure if there is a mistake? So I decide to delete ' and then it turns black color which I think I do the right thing. 8. I protect worksheet then hit save and close 9. I close file and reopen it. 10. but I still can't do the expand or collapse the groupped columns. PLease kindly help ToMMie "Gord Dibben" wrote: !. You have many replies and suggestions for this at your other thread. I can add nothing to those. 2. Enter this code in Thisworkbook module. Private Sub Workbook_Open() With Worksheets("Sheet1") .Protect Password:="justme", userinterfaceonly:=True .EnableOutlining = True '.EnableAutoFilter = True End With End Sub Note; the outliing and/or autofilter must be applied prior to saving and re-opening. Gord Dibben MS Excel MVP On Thu, 11 Sep 2008 10:01:01 -0700, ToMMie wrote: Thanks a lot. Well I'm have another few more questions. If you could help me out, it would be very kind of you. 1. to enter number in a cell, the lenght shouldn't be longer than 15 digits. but I need to key in 1111222233334444 into a cell but I need an auto format to display like 1111 2222 3333 4444. but the thing is that I format as custom like 0000 0000 0000 0000 but it shows 1111 2222 3333 4440. (problem!!) So, someone suggested me to key it as text and include spaces which is not convenient to my users. Do you know any other ways to do this? 2. I groupped 5 columns. Then, I protect the worksheet. but I just can't expand / collapse those groupped column. how can i fix that? Million thanks. ToMMie "Gord Dibben" wrote: Couple of methods on John McGimpsey's site One without VBA and one with. http://www.mcgimpsey.com/excel/timestamp.html Gord Dibben MS Excel MVP On Thu, 11 Sep 2008 08:56:01 -0700, ToMMie wrote: Hi all, For example, A1 is column for date input and B1 is for the time date is input in A1. If I want excel to automatically show time the date is input, what formula is used? Now, i wrote one formula but it doesn't really fit my requirement. I'm using like =if(a1<"",now()) what I get at frist place seems ok but I just realized that by using this formula, the time always changes. Please help ToMMie |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need a formula about time stamp....?
Hi Gord,
It works for me now. I really appreciate your kindness. Well, I' m having another problem next to this one. I share this workbook. and when I re-open it, it seems something error. Please kindly help. ToMMie "Gord Dibben" wrote: You were correct in removing the ' from .EnableOutLining = True The ' designates a non-running comment or remmed out line, in this case As far as you not being able to expand/collapse the grouped columns........the code works for me as advertised. You sure the sheet with the outlining is Sheet1? This is the only code you need in Thisworkbook. Private Sub Workbook_Open() With Worksheets("Sheet1") .Protect Password:="justme", userinterfaceonly:=True .EnableOutlining = True .EnableAutoFilter = True 'not applicable in your case, I think End With End Sub Gord On Fri, 12 Sep 2008 00:13:00 -0700, ToMMie wrote: Hi Dibben, Could you please help me a bit on No. 2? I'm not really sure whether or not I follow the right steps as I've never done with VB before. According to your suggestion, I do, first, group columns I want 1. go to Tools -- Macro -- Visual Basic Editor. It prompts me the windows of MS Visual Basic 2. Then I double click This Workbook module. A new windows pops up 3. At drop down menu on left top, I select Workbook 4. Next one, I select Open 5. There is a message in below pane like Private Sub Workbook_Open() End Sub 6. I copy the code and paste it in between like Private Sub Workbook_Open() With Worksheets("Sheet1") .Protect Password:="justme", userinterfaceonly:=True .EnableOutlining = True '.EnableAutoFilter = True End With End Sub 7. As I notice that font color of '.EnableAutoFilter = True turns green. I'm not sure if there is a mistake? So I decide to delete ' and then it turns black color which I think I do the right thing. 8. I protect worksheet then hit save and close 9. I close file and reopen it. 10. but I still can't do the expand or collapse the groupped columns. PLease kindly help ToMMie "Gord Dibben" wrote: !. You have many replies and suggestions for this at your other thread. I can add nothing to those. 2. Enter this code in Thisworkbook module. Private Sub Workbook_Open() With Worksheets("Sheet1") .Protect Password:="justme", userinterfaceonly:=True .EnableOutlining = True '.EnableAutoFilter = True End With End Sub Note; the outliing and/or autofilter must be applied prior to saving and re-opening. Gord Dibben MS Excel MVP On Thu, 11 Sep 2008 10:01:01 -0700, ToMMie wrote: Thanks a lot. Well I'm have another few more questions. If you could help me out, it would be very kind of you. 1. to enter number in a cell, the lenght shouldn't be longer than 15 digits. but I need to key in 1111222233334444 into a cell but I need an auto format to display like 1111 2222 3333 4444. but the thing is that I format as custom like 0000 0000 0000 0000 but it shows 1111 2222 3333 4440. (problem!!) So, someone suggested me to key it as text and include spaces which is not convenient to my users. Do you know any other ways to do this? 2. I groupped 5 columns. Then, I protect the worksheet. but I just can't expand / collapse those groupped column. how can i fix that? Million thanks. ToMMie "Gord Dibben" wrote: Couple of methods on John McGimpsey's site One without VBA and one with. http://www.mcgimpsey.com/excel/timestamp.html Gord Dibben MS Excel MVP On Thu, 11 Sep 2008 08:56:01 -0700, ToMMie wrote: Hi all, For example, A1 is column for date input and B1 is for the time date is input in A1. If I want excel to automatically show time the date is input, what formula is used? Now, i wrote one formula but it doesn't really fit my requirement. I'm using like =if(a1<"",now()) what I get at frist place seems ok but I just realized that by using this formula, the time always changes. Please help ToMMie |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need a formula about time stamp....?
Shared workbooks have their own set of problems when it comes to protection
and the running of code on protected sheets. I don't know how to code this into a shared workbook. Hopefully someone has the proper code for this issue and will post it here. Gord On Sat, 13 Sep 2008 00:56:00 -0700, ToMMie wrote: Hi Gord, It works for me now. I really appreciate your kindness. Well, I' m having another problem next to this one. I share this workbook. and when I re-open it, it seems something error. Please kindly help. ToMMie "Gord Dibben" wrote: You were correct in removing the ' from .EnableOutLining = True The ' designates a non-running comment or remmed out line, in this case As far as you not being able to expand/collapse the grouped columns........the code works for me as advertised. You sure the sheet with the outlining is Sheet1? This is the only code you need in Thisworkbook. Private Sub Workbook_Open() With Worksheets("Sheet1") .Protect Password:="justme", userinterfaceonly:=True .EnableOutlining = True .EnableAutoFilter = True 'not applicable in your case, I think End With End Sub Gord On Fri, 12 Sep 2008 00:13:00 -0700, ToMMie wrote: Hi Dibben, Could you please help me a bit on No. 2? I'm not really sure whether or not I follow the right steps as I've never done with VB before. According to your suggestion, I do, first, group columns I want 1. go to Tools -- Macro -- Visual Basic Editor. It prompts me the windows of MS Visual Basic 2. Then I double click This Workbook module. A new windows pops up 3. At drop down menu on left top, I select Workbook 4. Next one, I select Open 5. There is a message in below pane like Private Sub Workbook_Open() End Sub 6. I copy the code and paste it in between like Private Sub Workbook_Open() With Worksheets("Sheet1") .Protect Password:="justme", userinterfaceonly:=True .EnableOutlining = True '.EnableAutoFilter = True End With End Sub 7. As I notice that font color of '.EnableAutoFilter = True turns green. I'm not sure if there is a mistake? So I decide to delete ' and then it turns black color which I think I do the right thing. 8. I protect worksheet then hit save and close 9. I close file and reopen it. 10. but I still can't do the expand or collapse the groupped columns. PLease kindly help ToMMie "Gord Dibben" wrote: !. You have many replies and suggestions for this at your other thread. I can add nothing to those. 2. Enter this code in Thisworkbook module. Private Sub Workbook_Open() With Worksheets("Sheet1") .Protect Password:="justme", userinterfaceonly:=True .EnableOutlining = True '.EnableAutoFilter = True End With End Sub Note; the outliing and/or autofilter must be applied prior to saving and re-opening. Gord Dibben MS Excel MVP On Thu, 11 Sep 2008 10:01:01 -0700, ToMMie wrote: Thanks a lot. Well I'm have another few more questions. If you could help me out, it would be very kind of you. 1. to enter number in a cell, the lenght shouldn't be longer than 15 digits. but I need to key in 1111222233334444 into a cell but I need an auto format to display like 1111 2222 3333 4444. but the thing is that I format as custom like 0000 0000 0000 0000 but it shows 1111 2222 3333 4440. (problem!!) So, someone suggested me to key it as text and include spaces which is not convenient to my users. Do you know any other ways to do this? 2. I groupped 5 columns. Then, I protect the worksheet. but I just can't expand / collapse those groupped column. how can i fix that? Million thanks. ToMMie "Gord Dibben" wrote: Couple of methods on John McGimpsey's site One without VBA and one with. http://www.mcgimpsey.com/excel/timestamp.html Gord Dibben MS Excel MVP On Thu, 11 Sep 2008 08:56:01 -0700, ToMMie wrote: Hi all, For example, A1 is column for date input and B1 is for the time date is input in A1. If I want excel to automatically show time the date is input, what formula is used? Now, i wrote one formula but it doesn't really fit my requirement. I'm using like =if(a1<"",now()) what I get at frist place seems ok but I just realized that by using this formula, the time always changes. Please help ToMMie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
time stamp | Excel Worksheet Functions | |||
Time Stamp formula | Excel Worksheet Functions | |||
How do I lock a stamp date/time formula for an entry on a row? | Excel Worksheet Functions | |||
Time Stamp | Excel Worksheet Functions | |||
time stamp | Excel Worksheet Functions |