FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


Autoconvert cells in Excel question





Da Rossa
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 Smile
rafifaisal
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 Cool
Da Rossa
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 Cool


Almost there Very Happy 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 Sad
Do you know why? Smile
rafifaisal
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.
Da Rossa
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! Razz

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?
rafifaisal
So do you want it to be showing 14:24 instead of 2:24 PM ?
Again you can format this under Format Cells / Time...
Da Rossa
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"! Smile
Kelvin
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.
Da Rossa
Well, let's begin Very Happy
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... Sad

Thanks anyway!
Kelvin
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.
Da Rossa
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.
Kelvin
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.
rafifaisal
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 Smile
Da Rossa
Thanks anyway. Embarassed
Related topics
I Have a question . Is it the server support Chinese?
Question before requesting free hosting
question abt posts/mth
Kinda dumb question regarding DNS
Highlighting Table Cells
A question
UT2004 U like the game? Do u have any question post here.
Question about DNS!
Question about Domain!
another uni.cc question
Working with Excel, PHP & MySQL. Any Ideas
Excel question
Where to write VBA in Excel?
Excel question
Reply to topic    Frihost Forum Index -> Computers -> Software

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.