• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to convert values to labels in a spreadsheet column?

MUNCHDADDY


Firstly, Welcome to the Chandoo.org forums.


What do you mean Labels ?


Excel stores Numbers or Text in cells

You can use these or display them in a number of ways including as Dates, Currency, Numbers or just text.


Excel can also setup special areas of a Spreadsheet Called Tables which are like a flat file Database and can have Headers or Field Labels


Excel can also chart your data and display it as a number of Chart Types


Excel can also display data as Pivot tables which are special tables that can be used to easily manipulate data retrieval from an existing table or DB.


You don't need top specify data as Labels except in Charting


Can you be more specific about your requirement?
 
Tq Hui.

What i am after is actually to try to introduce an apostrophe before the number in the cell.

I can do it for one cell but when there is a stack of maybe fifty it gets tedious. Is there a formatting choice or a macro i can create?
 
Hi ,


If you want a range of cells to be taken as text , select the range , right click , select Format Cells , select Text , OK.


Now , if you enter your data , you don't need to use the apostrophe. Even a number entered in these 'formatted as text' cells , will be taken as text , or in your words , "label".


Narayan
 
If i put in 5:12:07 excel recognises this as " 5:12:07 AM"

If i put in 5:12:77 excel recognises this as 0.217558 ( fair enough as there are only 60 secs in a minute. Interesting to know how that entry is handled by excel).

I can go with the mid function to convert 5:12:07 AM into 312.07 seconds but the second entry cannot be manipulated for total duration in seconds at all. How do you get around this?

Thanks
 
Hi ,


If you enter a value such as 5:12:77 in Excel , it is converted to 0.217558 ; but if you format the cell where you entered this as Time , you will see 5:13:17 AM or 5:13:17 depending on the format chosen.


You don't have to use the MID function to convert the times to minutes ; just multiply by 60 x 24 , since there are these many minutes in a day.


Multiplying 5:12:07 by 60 * 24 gives 312.11666 minutes ; doing the same with 5:12:77 gives 313.28333


Narayan
 
Hi, MUNCHDADDY!


Asumming you have this 4 cells from A1:B2, where column A has general format and column B was reset to general format:

-----

[pre]
Code: 
05:12:07	0,216747685
5:12:77		0,21755787
[/pre]
-----

Where:

A1: 5:12:07 (general format)

A2: 5:12:77 (general format)

B1: =A1

B2: VALOR(IZQUIERDA(A2;ENCONTRAR(":";A2)-1))/24+VALOR(EXTRAE(A2;ENCONTRAR(":";A2)+1;ENCONTRAR(":";A2;ENCONTRAR(":";A2)+1)-ENCONTRAR(":";A2)-1))/24/60+VALOR(DERECHA(A2;LARGO(A2)-ENCONTRAR(":";A2;ENCONTRAR(":";A2)+1)))/24/60/60 ----- in english: =VALUE(LEFT(A2,FIND(":",A2)-1))/24+VALUE(MID(A2,FIND(":",A2)+1,FIND(":",A2,FIND(":",A2)+1)-FIND(":",A2)-1))/24/60+VALUE(RIGHT(A2,LEN(A2)-FIND(":",A2,FIND(":",A2)+1)))/24/60/60


There you can find the detailed formula calculation for the time value entered as text. The difference is 0.000810185 which is the numeric equivalente to 1'10", actual time difference.


Hope it helps.


Regards!
 
Hi, MUNCHDADDY!

Glad to help, thanks for your feedback and welcome back whenever needed or wanted.

Regards!
 
You must log in or register to reply here.
Back
Top