Hi,
I've heard that there is a way to (auto)convert some data inputted in a cell from this syntax:
3-8-7---2-40
To this one:
03/08/2007 at 02:40
The objective is to make it easier and faster to input dates and times, since the - is handier than the / in the numpad plus the fact that the zeros are necessary. Editing hundreds of cells directly in the second way is too painful.
Does anyone know how?
Tks 
Hi,
To format the date is easy. Just right click the cell and select Format Cells / Date and the format you want.
Now, to format the time in the same cell as the date, I don't know.
I think you will have to enter date and time in seperate cells...
Could be I am wrong though 
| rafifaisal wrote: |
Hi,
To format the date is easy. Just right click the cell and select Format Cells / Date and the format you want.
Now, to format the time in the same cell as the date, I don't know.
I think you will have to enter date and time in seperate cells...
Could be I am wrong though |
Almost there 
I followed you tip, inputted a "2-3-4" date format into a cell, right-click, format cells, then I chose the option that allowed it to automatically change to the format 02/03/04 (dd/MM/yy). However, I could not make the similar change to the time, and I don't know why. If I input something in the format "17-4", meaning that I want it to convert to 17:04, I doesn't work 
Do you know why? No man. Just tried everything I know out but invain.
Most probably you will have to customize the format. But it isn't working for me.
Sorry.
Do let me know if you have found it.
Oh come on man!! You helped me a lot already!! You know, I didn't have a clue about the 'format cells' option under the context menu! 
What happened right now: If I input "2,6" in a cell (in the Portuguese-Brazilian version, we use , instead of . for decimals, such as 2,6 = 2.6 in the US; 2,600,000 in there is rather expressed as 2.600.000 in here) incredibly it converts itself to 14:24 ! = 2:24 pm, we use the 24 hour format. THIS is kinda weird, isn't it? So do you want it to be showing 14:24 instead of 2:24 PM ?
Again you can format this under Format Cells / Time...
| rafifaisal wrote: |
So do you want it to be showing 14:24 instead of 2:24 PM ?
Again you can format this under Format Cells / Time... |
No! this time you didn't get it... What I want is, first, to know why "2,6" is being converted to "14:24"! I don't think it is possible that you use:
3-8-7---2-40
To this one:
03/08/2007 at 02:40
by just customising the excel date & time options. You can however still add the "AT" to the format by using this custom rule:
[$-409]mm-dd-yyyy \at h:mm AM/PM;@
For the above rule, you would be entering the data as eg. 7-8-7 9:23 (actual display: 07-08-2007 at 9:23 AM)
I doubt excel will be able to distinguish the difference of time and date stamp if both uses "-" as an identifier. This setting is done in control panel under "regional settings" whereby time uses ":" and date uses "-" or "/" (remember to set to "/" if you want it as "/").
Even if you changed [$-409]mm-dd-yyyy \at h:mm AM/PM;@ to [$-409]mm-dd-yyyy \at h-mm AM/PM;@ you will still have to enter 7-8-7 9:23 for the cell to display time correctly. In this case, it will be 07-08-2007 at 9-23 AM.
Your best option if you just want to use the keypad is to format the regional settings to use "." as the time identifier/separator instead of ":". This way, you can type it as 7-8-7 9.23 to get 07-08-2007 at 9.23 AM. Alternatively, you can try using the IF function to get your desired result.
Well, let's begin
Where do I change the regional settings? (Remember, it's Excel 2007)
Also, it appears that I'm gonna have to look for a Excel video tutorial or a "Excel for Dummies" to learn more...
Thanks anyway! Regional settings are found under the "Control panel" and not under Excel. Click on the "start" and select "Control panel", then click on "Date, Time, Language, and Regional Options". Look under "Regional & Language Options" then select "customize". You will be able to find the setting here. So have a go at it and change it to ur liking.
| Kelvin wrote: |
| Regional settings are found under the "Control panel" and not under Excel. Click on the "start" and select "Control panel", then click on "Date, Time, Language, and Regional Options". Look under "Regional & Language Options" then select "customize". You will be able to find the setting here. So have a go at it and change it to ur liking. |
I found it, thanks, but I'll forget it... I realized that it's not possible the way I want... I'm gonna have to use separate cells so the date and time would be formatted correctly.
Thanks Kevin, honestly.you can still get the whole load into the cell:
E.g. 08/08/2007 at 5:43 PM
However you will not be able to just enter them all from the keypad unless you changed the time separator to "." instead of the default ":". You will still need to use space bar to separate the values to get the display right:
E.g. 8/8/7 17.43 (this will get you the above result). You will still need to 1st custom format the cells you want the information in. Use the format I listed for you in the previous posting and you should not have any problem.
| Da Rossa wrote: |
... I realized that it's not possible the way I want... I'm gonna have to use separate cells so the date and time would be formatted correctly.
|
Just like I said Thanks anyway. 
