If you want to count the number of characters in a single cell, including white spaces, check out LEN. Want to know how many characters are in cell A1? Just type
=LEN(A1) into a different cell and you’ll find out.
This takes data from two cells and turns it into one. Check out our previous look at Excel functions to see how this can be useful.
Want to know the number of days between two dates in a spreadsheet? If you had for example, September 5, 2015 in cell A4 and December 27, 2015 in A5 just use
=DAYS(A5, A4) to get the answer. (Reversing the order of cells would give you a positive number).
Knowing the number of days is great, but if you need to know how many work days that range encompasses, turn to NETWORKDAYS (that’s ‘net work days’) instead. This function uses the same format as DAYS, but you need to use an ascending cell order to get a non-negative number. So
=NETWORKDAYS(A4, A5) will give you 80 and not -80.
Need to know the square root of 1764? Type
=SQRT(1764) into a cell, hit Enter, and you’ll find the answer to life, the universe, and everything—including the example formula.
Want to see the current date and time whenever you open a particular worksheet? Type =
NOW() into the cell where you want the date and you’re done. If you want to see a future date from right now, you can use something like
=NOW()+32. The now function does not take any arguments so don’t put anything in between the brackets.
As its name suggests, this function lets you round off numbers. ROUND requires two arguments: a number or cell, and the number of digits to round to. If you have the number 231.852645 in A1, for example,
=ROUND(A1, 0) gives you 232,
=ROUND(A1, 1) gives you 232.9, and
=ROUND(A1, -1) returns 230.
If you want more direct control over rounding up or down there are functions for that too. ROUNDUP and ROUNDDOWN use exactly the same argument format as ROUND. To learn more about round and its counterparts, check out Microsoft’s support pages.
Sign up for CIO Asia eNewsletters.