1. I want to be able to see the date for the next or current day of the week based on today's date? For the next or current Friday use:
=TODAY()-WEEKDAY(TODAY()-6)+7
This formula is very easy to adjust for other days as well, assume you want the next or current Monday?
=TODAY()-WEEKDAY(TODAY()-2)+7
2. How can I get the last day of the current month?
=DATE(YEAR(TODAY()),MONTH(TODAY())+1,0)
Note that this formula will fail if "transition formula evaluation" is turned on under tools>options>transition, many former Lotus 123 users have that option checked.
3. Can I get the date of the last Friday of any month? Yes, with any date from the particular month in A1
=DATE(YEAR(A1),MONTH(A1),29)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),2))
4. How can I test if a year is a leap year? With the year in question in A1:
=IF(DAY(DATE(A1,2,29))=29,"Leap Year","Not a Leap Year")
5. How can I do calculations with so called military time where instead of 8:00 you use 800? Assume that A1 holds 800 and B1 1700, the difference should be 9 hours. Here are a couple of ways:
=--TEXT(TEXT(B1-A1,"00\:00"),"hhmm") will return the result in military time as well
=--TEXT(TEXT(B1-A1,"00\:00"),"hh:mm") will return the result in regular time
=INT(B1/100)/24+MOD(B1,100)/100/24-INT(A1/100)/24+MOD(A1,100)/100/24 (is a more traditional conversion)
6. Some of my employees start working between 08:00 PM and 09:30: PM and stop working between 04:30 AM and 06:00 AM, how can I get the numbers of hours worked?
=MOD(B1-A1,1) with start time in A1 and and time in B1, another way is:
=B1-A1+(A1>B1) which probably is a bit easier to understand
7. Is there a way to create a formula for test score grades? Sure, the formula can be adapted for + and minus scores if necessary:
=IF(A1="","",LOOKUP(A1,{0;61;71;81;91},{"F";"D";"C";"B";"A"})) where one put the test score in A1
8. Can COUNTIF use more than one condition? No, but you can use SUMPRODUCT, assume you want to count "a" in A1:A10 if B1:B10 is greater than 4.
=SUMPRODUCT(--(A1:A10="a"),--(B1:B10>4))