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 🙂
PS: Make sure you attempt the challenges and post your answers in comments.
18 Responses to “Best Charts to Compare Actual Values with Targets – What is your take?”
Great post. I can't vote, though, because the answer I want to put down is "it depends". As with all visualisations, you've got to take into account your audience, your purpose, technical skills, where it will be viewed, etc.
I'm with Andy: It depends. Some I would use, some I might use, some I won't touch with a barge pole.
Naturally I have comments 🙂
The dial gauge, though familiar, is less easy to read than a linear type of chart (thermometer or bullet). It's really no better than the traffic lights, because all it can really tell you is which category the point falls in: red, yellow, or green.
By the same token, pie charts are so familiar, people don't know they can't read them. Remember how long it takes kids to learn to read an analog clock?
Bullet charts don't show trends.
With any of the charts that have a filled component and a marker or ine component, it makes more sense to use the filled component (area/ column) for target, and the lines or markers for actual.
[...] Best Charts to Compare Actual values with Targets (or Budgets … [...]
I voted for #6 even though I agree with the other comments that it depends.
The majority of the votes are for the #2, thermometer chart. I still have yet to understand what happens when you are above plan/goal, which was brought up in yesterday's post.
Also, I agree with Jon in that it would be better to flip the series and make the filled part the target or goal and the line or marker the actual.
I am also a fan of using text when appropriate if the data is among other metrics in a type of dashboard. Calling it out by saying actual and % achievement is a good option.
Another "it depends" vote. Are you just looking at one or are you comparing a number of targets with actuals? You didn't include a text box. The problem with sentences is that they can get lost in a page of gray text. A text box can call attention to the numbers and line them up effectively.
I'm with Jon: "Some I would use, some I might use, some I won’t touch with a barge pole" and I'm surprised that some of your readers voted for the last group.
Jon says:
With any of the charts that have a filled component and a marker or line component, it makes more sense to use the filled component (area/ column) for target, and the lines or markers for actual.
Why does this make more sense? I like 6 the way it is, although I would use a heavy dash for the plan/target marker.
"It depends" is also my take. What I usually try to drill into my clients dashboard design is the fu ndamental difference between spot results (am I on target for this month) and long term trends.. I always try to create 3 different set of graphs to represent real perormance:
- spot results vs objectives
- cumulative results vs objectives
- long-term trend (moving average) mostly) to see where we're going
[...] Best Charts to Compare Actual Values with Targets – What is your take? (tags: excel charts) [...]
[...] Related: Charting Principles, How to compare actual values with budgets [...]
[...] Excel Charting Alternatives to compare values [...]
Jon says:
With any of the charts that have a filled component and a marker or line component, it makes more sense to use the filled component (area/ column) for target, and the lines or markers for actual.
Why does this make more sense? I like 6 the way it is, although I would use a heavy dash for the plan/target marker.
I totally agree, Bob. I would normally favour a line for the target and a column for the actual, you can see quite easily then which columns break through the line, then.
[...] best charts to compare actual values with targets — den Status mal anders zeigen, z. B. als Tacho [...]
Thermometer charts: "Not appropriate when actual values exceed targets" - this is easily solved by making the "mercury" portion a different color from the border, then you can clearly see where the expected range ends and the actual values keep going.
People seem to knock gauges quite a bit in dashboarding, but trying to show comparison of realtime data between operating sites and targets for each site can easily be done with a bank of gauges that have the optimal operating points at 12 o'clock.
The human eye is great at pattern stripping, and any deviation of a gauge from the expected 12 position will quickly register with an operator and attract his attention. Using a colour background, or meter edge, will also indicate the sensitivity of a particular site.
[…] Best charts to compare actual with target values […]
[…] Best charts to compare actual with target values […]
[…] work laptop I have a favorites folder just dedicated to Excel charts. Its got things like “Best Charts to Compare Actuals vs Targets” and “Best charts to show progress“. I love me some charts […]
I am wondering how will the plotting work, for some of the targets which may have been achieved before time. E.g. for the month of Jul the target was 226 and the actual was 219. So the chart will show a deficit in meeting the target by 7 points but what if this 7 may have been completed earlier in month of June. So ideally it not a deficit.