IF(LEN) Function

[shanenicholson88]

Good morning all,

I have the following formula on one of my spreadsheets, written by an excel wizard. I am trying to figure out what exactly this is doing. I know what the functions do individually, but not like thiis.
Any help will be much appreciated, and thank you in advance to taking the time to read it.

=IF(LEN(M6)=3,LEFT(M6,1),LEFT(M6,2))*100

 

[alansidman]

If the length of cell M6 is equal to 3 then put the first character in M6 and multiply it by 100. If the length of M6 is something other than 3 then the first two characters of M6 and multiply by 100.

Example: M6 = 125 then the result would be 1 times 100 or 100
If M6 = 1250 then the result would be 12 times 100 or 1200

 

[shanenicholson88]

If the length of cell M6 is equal to 3 then put the first character in M6 and multiply it by 100. If the length of M6 is something other than 3 then the first two characters of M6 and multiply by 100.

Example: M6 = 125 then the result would be 1 times 100 or 100
If M6 = 1250 then the result would be 12 times 100 or 1200

Thats very kind of you to get back to me so quickly, much appreicated.
OK, so in cell M6 I have a date

08-JUN-01 or 08/06/1901 and in the cell this formula is in, I get the result 500.

Any Idea?

Thanks again Alan..

 

[alansidman]

Excel dates are just numbers formatted to look like dates. Look at this link to understand

How Dates Work in Excel - The Calendar System Explained + Video - Excel Campus

This post and video explain how the date calendar system works in Excel.

www.excelcampus.com

June 6, 1901 is the numerical equivalent of 525. Hence 5 times 100 is the result.

 

[shanenicholson88]

Excel dates are just numbers formatted to look like dates. Look at this link to understand

How Dates Work in Excel - The Calendar System Explained + Video - Excel Campus

This post and video explain how the date calendar system works in Excel.

www.excelcampus.com

June 6, 1901 is the numerical equivalent of 525. Hence 5 times 100 is the result.

You have seriously made my day! Thank you so much for explaining that..

 

[sandy666]

Book1
	M	N
6	08/06/1901	500
7	08/06/1901	800
Sheet1

Cell Formulas
Range	Formula
N6	N6	=IF(LEN(M6)=3,LEFT(M6,1),LEFT(M6,2))*100
N7	N7	=IF(LEN(TEXT(M7,"dd/mm/yyyy"))=3,LEFT(TEXT(M7,"dd/mm/yyyy"),1),LEFT(TEXT(M7,"dd/mm/yyyy"),2))*100

the result depends on what formula you use