Posted: 7/27/2009 7:16:42 AM EDT
|
Guys and gals,
I am trying to find a way to program an excel spreadsheet to make a permanent time stamp when data is entered into a cell. This is for dispatching / electronic run card file. I currently type in this information by hand with each run, but I'd like to automate it. I found a way for it to put in a date and time but every time I re-open the file it refreshes all the times to current, whereas I'd like it to be permanent... It is Excel 2007 if that makes a difference... Any suggestions? Here is a screen shot to give an example of what I have going... I want to automate the "time" column And no we don't have money for a CAD :-P Thanks for asking. 
|
wow, more dispatchers are making due w/o CAD than I thought 
http://tinyurl.com/ldk96x |
|
I just realized I left my Excel book at home, but if you Bing "Excel Forum", you will find a place that can do anything with Excel except make Obama look good.
Ah, hell. Here's the link: http://www.excelforum.com/ |
|
I already googled it, I've only found people with my same problem and no solutions. Zaphod - thanks i will have to check that at home |
| The best way I can think of it is to do two parts. If you already have a function in the time column to put in the current time when the information is input that is fine. To avoid everything changing like you said where it updates it to the current time when the workbook opens instead have a macro run when the workbook is closed that or run when you choose that copies the value of the cell over the formula and loop through the whole column. |
|
Quoted:
I already googled it, I've only found people with my same problem and no solutions. Zaphod - thanks i will have to check that at home there were several different options there, that's why I submitted the link.. you need to find one that will work for you. I know it sux using excel. I have a customer that was in the same boat. they got tired of the limitations of Excel as the pd/fd requirements grew. they finally ponied up and bought some cad software. see if you can get the PD, FD and EMS (and/or whom ever uses dispatch services) and see if they could go in together for an inexpensive cad system. you'll need to do your homework on what benefit they'd provide to the departments. This can be done with the help of the CAD software companies. they want you to buy their product and they will help you do so by answering all your questions, providing documents to support what ever posistion YOU want. some can even provide a time trial (demo) 
|
|
Quoted:
Quoted:
I already googled it, I've only found people with my same problem and no solutions. Zaphod - thanks i will have to check that at home there were several different options there, that's why I submitted the link.. you need to find one that will work for you. I know it sux using excel. I have a customer that was in the same boat. they got tired of the limitations of Excel as the pd/fd requirements grew. they finally ponied up and bought some cad software. see if you can get the PD, FD and EMS (and/or whom ever uses dispatch services) and see if they could go in together for an inexpensive cad system. you'll need to do your homework on what benefit they'd provide to the departments. This can be done with the help of the CAD software companies. they want you to buy their product and they will help you do so by answering all your questions, providing documents to support what ever posistion YOU want. some can even provide a time trial (demo) RED - I just went back and actually let the LMGTFY do its thing and I saw the links it had, that actually had quite a few good ideas, sorry to blow it off so quickly. I am having a busy day and figured it would return the same results I got |
|
Quoted:
The best way I can think of it is to do two parts. If you already have a function in the time column to put in the current time when the information is input that is fine. To avoid everything changing like you said where it updates it to the current time when the workbook opens instead have a macro run when the workbook is closed that or run when you choose that copies the value of the cell over the formula and loop through the whole column. That's what I was thinking, but it's the latter part that had (has) kme stumped. |
|
Quoted: Quoted: Change the column to "Text" and not time? Could you elaborate? Well, from what I'm understanding.. you're manually entering the Time right? But you let Excel change the cell format to "time"., so when you reopen your document excel changes the time to the current time? If you change the cell format to standard text, then the cell contents will never change. |
|
Quoted:
Quoted:
Quoted:
Change the column to "Text" and not time? Could you elaborate? Well, from what I'm understanding.. you're manually entering the Time right? But you let Excel change the cell format to "time"., so when you reopen your document excel changes the time to the current time? If you change the cell format to standard text, then the cell contents will never change. Incorrect. I am manually entering time because using the NOW() with a macro to enter it will refresh every time I open the document... |
|
Oh. So your Macro is entering the time but you're having to go back and manually re-enter a time because the Macro time changes to Current when you re-open the doc? Want to post the Macro so we can dissect it? My guess would be your Macro is using something along the lines of CURRENT_TIME instead of just a TIME_STAMP. I guess it just depends on the language and the variables ETA, instead of using NOW() try replacing it with ADDTIME() Lots of variables here, try plugging in a couple different ones until you get what you want http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_addtime |
|
Quoted:
Oh. So your Macro is entering the time but you're having to go back and manually re-enter a time because the Macro time changes to Current when you re-open the doc? Want to post the Macro so we can dissect it? My guess would be your Macro is using something along the lines of CURRENT_TIME instead of just a TIME_STAMP. I guess it just depends on the language and the variables ETA, instead of using NOW() try replacing it with ADDTIME() Lots of variables here, try plugging in a couple different ones until you get what you want http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_addtime lol. you sent him to the MySQL user manual. to the OP: some syntax may be similar, but that has nothing to do with Excel. |
|
Quoted: Quoted: Oh. So your Macro is entering the time but you're having to go back and manually re-enter a time because the Macro time changes to Current when you re-open the doc? Want to post the Macro so we can dissect it? My guess would be your Macro is using something along the lines of CURRENT_TIME instead of just a TIME_STAMP. I guess it just depends on the language and the variables ETA, instead of using NOW() try replacing it with ADDTIME() Lots of variables here, try plugging in a couple different ones until you get what you want http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_addtime lol. you sent him to the MySQL user manual. to the OP: some syntax may be similar, but that has nothing to do with Excel. I'm an Admin, not a programmer |
|
Quoted:
I'm confused. In Excel 2007, the keystroke combination CTRL-SHIFT-; (control shift semi-colon) inserts the current time as a static cell entry. It does not change when you reopen the file. Why doesn't that work? It does work but that is slower than me just going "721 or 1130" or whatever on my numpad. I did find a solution and after much tweaking we have it working now, 2nd shift is pleased as well. Thank you all for your assistance :) |