42 tips for Excel time travelers

Today, let’s travel in time.  Pack your photon ray guns, extra underwear, buckle your seat belts and open Excel! Of course, we are not going to travel in time. (Come to think of it, we are going to travel in time. By the time you finish reading this, you would have traveled a few minutes) We are going to learn how to travel in time when using Excel. In simple terms, you are going to learn how to move forward or backward in time using Excel formulas. So are you ready to hit the warp speed? Let’s beam up our Excel time machine. Tip 0 – Date & Time are an illusion Most important tip for Excel time travelers is to understand that Excel dates & times are just numbers. So when you see a date like 17-October-2013 in a cell, you can safely assume that it is a number disguised to look like 17th of October, 2013. To see the number behind this, just select the cell and format it as number (from Home ribbon). Now that you understood this concept, let’s jump in to the 42 tips. All these tips assume a date or time value is in the cell A1. Staying at present: To have latest star date in a cell, just press CTRL+; (of course, in Excel world, star date is nothing but whatever date your computer shows) To have current time in a cell, just press CTRL+: Of course, we time travelers are lazy. So pressing CTRL+; every day or CTRL+: every second is not cool. That is why you can use =TODAY() in a cell to get today’s date. It will automatically change when you re-open the file tomorrow. Likewise, use =NOW() to get current date & time in a cell. Remember, although time changes every second, you will not see the cell updated unless the formula is somehow re-calculated. This is done by, Pressing F9 Saving / re-opening the file Making any changes to any cell (like typing a value, changing a value) Editing the formula cell and pressing Enter To check if today is after or before the date in cell A1, you can use =TODAY() > A1. This will be TRUE if A1 has a past date and FALSE if A1 has a future date. To know how many days are there between TODAY and the date in A1, use =TODAY() – A1. This will be a negative number if A1 is a future date. To see just the number of days (without negative sign), you can use =ABS(TODAY()-A1) To know how many hours are left between the time in A1 and current time, use =(NOW()-A1)*24. While the above formula works, it shows hours and fraction. To just see hours and minutes left, you can use =TEXT((NOW()-A1), “[hh]:mm”). Note: This formula works only when A1 < NOW(). To know how many weeks are left between TODAY() date and a future date in A1, use =(TODAY() - A1)/7 To know how many months are left between TODAY() and date in A1, use = DATEDIF(TODAY(), A1, “m”). Related: How to use DATEDIF function. To know which month is running, use =MONTH(TODAY()) To see the month name instead of number, use =TEXT(TODAY(), “MMMM”). This shows the month’s name in your Excel language. To know which year is running, use =YEAR(TODAY()) To see the last 2 digits of the year, you can use =RIGHT(YEAR(TODAY()), 2) To find the day of week for TODAY, use =WEEKDAY(TODAY()). This will give a number (1 to 7, 1 for Sunday, 7 for Saturday). To see the weekday name instead of number, use =TEXT(TODAY(), “DDDD”). To see today’s date alone, use =DAY(TODAY()) To know if the present year is a leap year or not, see this. Going back in time To go back by 6 days from the date in A1, use =A1-6 To go back to last Friday use =A1-WEEKDAY(A1, 16). This works in Excel 2010, 2013. If your time machine is old (ie you have Excel 2003 or earlier versions), you can use =A1-CHOOSE(WEEKDAY(A1), 2,3,4,5,6,7,1) To go back by 5 weeks, use =A1-5*7 To go back to start of the month, use =DATE(YEAR(A1), MONTH(A1),1) To go back to end of previous month, use = DATE(YEAR(A1), MONTH(A1),1) – 1 Or use =EOMONTH(A1,-1) To go back by 2 months, use =EDATE(A1, -2) To go back by 27 working days, use =WORKDAY(A1, -27). This assumes, Monday to Friday as working days. To go back by 27 working days, assuming you follow Monday to Friday work week and a set of extra holidays, use =WORKDAY(A1, -27, LIST_OF_HOLIDAYS) To go back by 7 quarters, use =EDATE(A1, -7 * 3) To go back to the start of the year, =DATE(YEAR(A1), 1,1) To go back to same date last year, = DATE(YEAR(A1)-1, MONTH(A1), DAY(A1)) To go back a decade, =DATE(YEAR(A1)-10, MONTH(A1), DAY(A1)) Going forward in time We, time travelers are smart people. Once you know that turning the knob backwards takes you to past, you know how to go to future. So I am giving very few examples for going forward in time. To go to the 17th working day from date A1, assuming you use Sunday to Thursday workweek, use =WORKDAY.INTL(A1,17,7). This formula works in Excel 2010 or above. To go to next hour, use=A1+1/24 To go to next day morning 9AM, use =INT(A1+1) + 9/24 To go to 18th of next month, use =DATE(YEAR(A1), MONTH(A1)+1, 18) To go to end of the current quarter for date in A1, use =DATE(YEAR(A1), CHOOSE(MONTH(A1), 4,4,4,7,7,7,10,10,10,13,13,13),1)-1 To go to a future date that is 4 years, 6 months, 7 days away from A1, use =DATE(YEAR(A1)+4, MONTH(A1)+6, DAY(A1)+7) Finding the amount of time traveled To know how many days are between 2 dates (in A1 & A2), use =A1-A2 To know how many working days are between 2 dates, use =NETWORKDAYS(A1, A2) (remember: A1 should be less than A2). Fixes for common time travel hiccups If you see ###### instead of a date in a cell, try making the column wider. If you still see ######, that means the date value is not understandable by Excel (negative numbers, dates prior to 1st of January 1900 etc.) Often when pasting date values in to Excel, you notice that they are not treated as dates. Use these techniques to fix. If you pass in-correct values or use wrong parameters, your date formulas show an error like #NUM or #VALUE. Read this to understand how to fix such errors. Quiz time for time travelers I see that you safely made it here. I hope you had a good journey. Let me see how good your time traveling is. Answer these questions: Write a formula to take date in A1 to next month’s first Monday. Given a date in A1, find out the closest Christmas date to it. Building your own time machine? Check out these tips too If you work with date & time values often, then learning about them certainly pays off. Read below articles to one up your time travel awesomeness. Using Date & Time in Excel How to calculate common holiday dates in Excel? How to calculate payroll dates? How to sort a bunch of birth dates by birthday? Check if two dates are in same month Good luck time traveling. I will see you again in future

Formula Forensics No. 035 Average the last 3 values greater than 0

A couple of weeks ago Amanda asked a question at Chandoo.org “I need to calculate a moving average of the last 3 months. However, if one of the months contains 0%, I want it to ignore that and take the last month that didn’t have a zero. For instance, my data is this: April = 100%, May=0%, June=97%,July=98%, August=0%. My formula is only looking at June July and August, but since August has a 0 I would like it to look at May June and July. And since May has a 0, I would like it to look at April, June and July.” I offered a solution which is an array formula. =AVERAGE(AVERAGEIFS($B$3:F3,$B$2:F2,LARGE(IF($B$3:F3>0,$B$2:F2),{1,2,3}))) Ctrl+Shift+Enter Today I am going to try and explain what it is doing and how it works. As always at Formula Forensics you can follow along with a sample file: Download Here The Problem How do we write a formula to extract the last 3 non zero values? What if there is more than 1 zero value? What if the zero values are non-contiguous? This can all be shown by: Average of the last 3 records – No Zeroes Average of the last 3 records – One Zero in Current Month Average of the last 3 records – One Zero  in a Previous Month Average of the last 3 records – Multiple Zeroes A Solution =AVERAGE(AVERAGEIFS($B$3:F3,$B$2:F2,LARGE(IF($B$3:F3>0,$B$2:F2),{1,2,3}))) Ctrl+Shift+Enter Normally at Formula Forensics we start in the inside of a formula and work out, but today we are going to start at the outside and work our way in. The solution: =AVERAGE(AVERAGEIFS($B$3:F3,$B$2:F2,LARGE(IF($B$3:F3>0,$B$2:F2),{1,2,3}))) This function is the Average() of an Averageifs() function. What is going on here you ask ? We know that the Average() function will average the constituent numbers. eg: =Average(6, 8, 10) = 8 But doesn’t Averageifs return a single number? The average of its components! We’ll mostly, but not always. Lets have a look: If we remove the outside average and just look at the inner Averageifs() function In the sample file, Cell F15 you will see: =AVERAGEIFS($B$3:F3,$B$2:F2,LARGE(IF($B$3:F3>0,$B$2:F2),{1,2,3})) Press F2 then F9, Excel evaluates this as: ={0.5,0.9,0.7} So the Averageifs() function is returning 3 values being 0.5, 0.9 & 0.7 These are the last 3 values greater than 0 ranked from latest to earliest by date Which is exactly what Amanda asked us to average So we will need to look inside the Averageifs() function to see what is going on. The Syntax for the Averageifs() function is: AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], …) in our example Average_range : $B$3:F3 This is the Values we want to average from teh start of the data up to the current cell Criteria_range1 : $B$2:F2 This is the Date Range from the start of teh data up to the current cell Criteria1 : LARGE(IF($B$3:F3>0,$B$2:F2),{1,2,3}) This is a function that will return the 3 largest values shown by the array {1,2,3} So in in English, the Averageifs function is being asked to return a single value from the Value row where the Date row matches the largest, second largest and third largest criteria. In each case Averageifs will average the number but as it is a single number it returns the value by itself. Lets now step into the Large() function and see what is going on. LARGE(IF($B$3:F3>0,$B$2:F2),{1,2,3}) The Large() function has the syntax =Large(Array, k) In our example: Array: IF($B$3:F3>0,$B$2:F2) k: {1,2,3} This is an array and hence asks for the Largest (1), Second largest (2) and Third Largest (3) values So we are getting the 3 largest values from the formula: IF($B$3:F3>0,$B$2:F2) What is the IF($B$3:F3>0,$B$2:F2) formula doing? If you put the formula IF($B$3:F3>0,$B$2:F2) into a blank cell say F31 and press F9 Excel returns: {41275,41306,41334,41365,FALSE} These are the date values of the date row up to the Column we are working in You can see these in Row 1 of the sample file You will notice that there is a False value in the position of the Column which has the Value of 0% This is derived by the If() Function. The If() function is saying: IF($B$3:F3>0,$B$2:F2) If the Value Row >0 ($B$3:F3>0), return the Date Row ($B$2:F2) else return False The False isn’t shown in the formula, it is returned by Default when a False argument doesn’t exist The standard Syntax for If is =If(Criteria, Value when true, Value when false) In our case we don’t have a Value when false component and so Excel simply places a false in as the answer. Applying the formula using Ctrl+Shift+Enter forces Excel to Evaluate the formula as an Array Formula What this means in practice is that the Formulas are evaluated 3 times as per the array {1,2,3} effectively extracting the last 3 values that match the last 3 dates where the Value is >0 What if I want to Average a Different Number of Days? You have two choices 1. Change the Array Manually If say you want to average the previous 5 values You can modify the array manually =AVERAGE(AVERAGEIFS($B$3:F3,$B$2:F2,LARGE(IF($B$3:F3>0,$B$2:F2),{1,2,3,4,5}))) Ctrl+Shift+Enter This is ok if it is not done regularly or is only slightly different to the existing array. But if you want to setup the top twenty you need to type {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20} Which is quite clumsy! Bad Hint: You can copy the array from above. Or you can automate it using the technique below. 2. Insert a Formula to Setup the Array You can add a small function that will automatically setup the array like: =AVERAGE(AVERAGEIFS($B$3:F3,$B$2:F2,LARGE(IF($B$3:F3>0,$B$2:F2),ROW(OFFSET($A$1,,,F39,1))))) Ctrl+Shift+Enter This assume that cell F39 has a number which is the number of periods you want to average You can read more about how the ROW(OFFSET($A$1,,,F39,1)) part works in previous Formula Forensics posts Download You can download a copy of the above file and follow along, Download Sample File. Other Posts in this Series The Formula Forensics Series contains a wealth of useful solutions and information. You can learn more about how to pull Excel Formulas apart in the following posts:http://chandoo.org/wp/formula-forensics-homepage/ Two Challenges 1. Can you solve this another way Just after I posted my solution, Chandoo posted an alternative solution which you can read at: http://chandoo.org/wp/2009/04/28/calculate-moving-average/#comment-446559 Can you solve this problem another way ? Let us know in the comments below: 2. Your Challenge? If you have a clever formula and would like to become an author here at Chandoo.org please consider writing it up as I have done above. Alternatively you can send the formula to either Chandoo or Hui.