Highline Excel Class 27: Custom Number Format & TEXT function

See how to use Custom Number Format for Cells and the TEXT function:
1)Format cells
2)Format Formula input numbers to show a label with Custom Number Format
3)TEXT function used with Text formulas to create labels
4)TEXT function to create TRUE FALSE formulas to check whether criteria is met
5)Count number of dates in a data set that meet criteria (certain month)

This is a beginning to advanced Excel class taught at Highline Community College by Mike Gel excelisfun Girvin Busn 214 BTech 109

Category:

Education

Tags:

• Excel
• Custom
• Number
• Format
• TEXT
• function
• Labels
• cells
• excelisfun
• Mike
• Gel
• Girvin
• Highline
• Community
• College

[chor1962]

Hi Proffesor

Thanks for the reply, i really could not get this one i was stuck with this vedio the whole day yesterday, for e.g lets say i have in the cell A1 2:00 AM and in the cell A2 i have 5:00 AM, now if i do =A1*A2 it gives me 0.017361 which i think is right the result does not look at the time format but lets say i change A2 to 1 and then do =A1*A2 the result i get is 2:00 AM shouldnt it return 0.08333*1 =0.08333

Thanks for all the help.

Regards

Chor

[ExcelIsFun]

You said:

i change A2 to 1 and then do =A1*A2 the result i get is 2:00 AM shouldnt it return 0.08333*1 =0.08333

But if you apply the General Format to the 2:00 AM, you will see 0.08333. The underlying number that the formula calculates will always be there no matter what the format.

Try this video I made 1 week ago:

Excel Magic Trick 450: Number Formatting Abbreviated Notes (House Paint or Halloween Mask)

[ExcelIsFun]

Also, sometimes Excel is programmed to carry a format forward (like when you got 2:00 AM), but no matter what the format the number underneath will always be the same.

[ExcelIsFun]

Another way to look at how 'Formulas Do Not See Formatting' is to put:

8:00 AM (time in) in cell A1

5:00 PM (time out) in cell A2

$10 (wage per hour) in cell A3

then the formula:

=(A2-A1)*A3

In cell A4

The answer will be $3.75 instead of $90 because the formula does not see the actual times, but instead it sees decimals under the time. It is the number underneath that the formula looks at, no matter what the format.

[chor1962]

Hi Professor

Sorry if iam posting this question multiple times, in this video you said formulas dont look at formatting but when i click on a empty cell in a sheet and type in =A21 (where A21 is the cell with time2:00 PM in this sheet) instead of getting .45833 i get the actual time , why is this happening when formulas dont look at formatting shouldnt it return the number .458333

Thanks again

Regards

Chor

[ExcelIsFun]

That is a smart question!

The calculating part of the formula never looks at formatting. But the diplayed answer can have formatting; and it is common that date and time formulas get formatting from the formula input cells.

Does that make sense?

[chor1962]

Thanks for the reply Professor

Regards

Chor