Excel- The Countdown to Christmas

Have you ever wondered how many shopping days are left until Christmas or what day of the week is Boxing day? How many days until payday? Or which day of the week is my birthday? Maybe you're forever asking yourself am I Wednesday's child or Friday's child?

Hurl your diary away, spurn your smartphone, ignore your fingers and turn to Excel for the answers to all these date conundrums. All Excel dates are stored as numbers and a few simple formulas and functions combined with a passing knowledge of date format codes is all you will ever need. Just make sure that you type-in your dates correctly otherwise you won't be able to use them in your calculations and your date formats won't work.

Entering Dates

Whenever you type a date into an Excel cell remember to use slash or dash separators like this: 9/12/14 or like this 9-12-14. It's years since we had to worry about the Millennium bug but if you're feeling fussy or you need a date in the last century then use 4 digits for the year i.e. 9/12/2014. Simply type-in the day and month and Excel presumes that it's the current calendar year so entries like this 9/12 or 9-12 will suffice.  Please don't use dot separators like this, 9.12.2014. They won't work unless you first change your Windows Control Panel settings.

The main thing is to make sure that the entry you make evaluates properly to a date and the best visual check is to see whether your date aligns to the right hand side of the cell but its always worth checking in the formula bar to see whether your date looks like this, 09/12/2014. When a cell contains a date you can make the date appear however you like by applying a date format.

How many shopping days to Christmas?

I am going to presume that you will be able to shop on a Sunday but you will not want to venture forth on Christmas day itself so we need to work out the number of days between today and the 24th December. 

Not long to go!

We want our calculation to show the number of dates automatically as time passes so rather than type-in today's date as a constant it is best to use Excel's TODAY function which always returns the current date.

In the illustration 24/12/2014 is entered into cell A1 and the formula =TODAY( ) is entered into cell A2. Don't forget the brackets for the function, nothing is required inside them but they have to be there for Excel to recognise the function call.

When you make your entry you only need type the first, opening bracket, the formula is not case sensitive so it is quite acceptable to just type-in "=today(". The final job is to calculate the difference in days between the two dates, for this we enter a simple formula in another cell to minus the earlier date from the later date, in cell A3 the formula is =A1-A2.

Come back tomorrow, the difference will one day less and you still haven't done your cards. Have you?

What day of the week is Boxing Day?

Now, I'm quite a sharp fellow and I know that Boxing day is the 26th December, the day after Christmas day. I even used to know why it is called Boxing day but what I don't know is what day of the week it is. No problem. Firstly enter 26/12/2014 into a convenient cell, then right-click and choose Format Cells from the shortcut menu. Click the Number tab and then Custom at the end of the category list. Don't go to the Date category as Excel does not have a standard format to show the day of the week, instead we are going to create one.

Formatting dates using a Custom Number format code

Date Format Codes

Excel date format codes are made up of combinations of d, m and y codes for day, month and year values using as many or as few as you need. For example, d gives you today's date as significant digits (1 as 1, 26 as 26), dd gives you today's date as two digits (1 as 01, 26 as 26), ddd gives you the short day of the week i.e. Fri and dddd gives you the full day of the week i.e. Friday.

Use the existing date codes as a pattern and examine the results of your format code in the Sample box as you type the code elements into the Type box. You can always re-use any format codes that you have already created in this workbook—they are stored at the end of the Custom list.

Enter your dddd code, click OK and now your cell shows that, this year, Boxing day is on Friday.

The Excel Christmas Date Challenge

For your homework tonight you have to:

1. Assuming you've already received your December salary and blown it. Calculate how many days you have to survive until your next pay day on 30th January 2015.
2. Work out on which day of the week you were born. 

Remember that it goes like this:

Monday's child is fair of face,
Tuesday's child is full of grace,
Wednesday's child is full of woe,
Thursday's child has far to go,
Friday's child is loving and giving,
Saturday's child works hard for a living,
And the child that is born on the Sabbath day is bonny and blithe, and good, and gay.

Does the old rhyme work for you? I was born on a Friday and while I have been called many things I don't remember it ever being loving or giving.

Related Posts

Excel-Tick marks and Check boxes in cells

Excel-Make Pivot Tables refresh automatically

Excel-Calculations without formulas

Excel-Sorting by last name

Excel-Switching columns to rows

Excel-CTRL key tricks

Excel-Double click tricks

Excel-Converting USA dates to UK dates

Excel Top ten shortcut keys

Excel 2010 shortcut keys

Excel-AutoSum Revisited

Excel for Mac-Deleting a range and other shortcuts

Excel-Calculating age from date of birth

Word- No footers on the last page

You don't always want to have exactly the same set of headers and footers all the way through a document. While you can change your headers and footers at any point in your document by inserting section breaks it is not always the perfect solution.

For example, many legal documents like Wills or Witness Statements require that each page should be individually signed at the foot of the page but then, on the last page of the document, is a more general declaration that is signed and dated and here the signature footer is not required. This general declaration is not on a separate page, it follows on from the last paragraph.

In this article we are going to demonstrate creating a lawyer's Witness Statement using Word field codes to control whether footers should be displayed or not by testing for the last page and also how to enter the Witness' name once and then have it copied wherever else it is required in the body of the document. The same methods can be usefully employed on many other different types of commercial document such as contracts or service agreements.

Copying the Witness name

We want to type-in the witness’ name at the top of the document and then have it copied into the footers and at various locations in the rest of the main document automatically without our having to repeatedly copy and paste it. Our method is to create a Bookmark and then insert a REF field code which refers back to the bookmark wherever the witness’ name is required. When you change the text in the bookmark you just update all your REF fields and the new text is copied in one go.

The Witness name is entered at the start of the document

The placeholder text (“Jehovah”) for the bookmark is at the top of the document, select it and then click Bookmark on the Links group of the Insert tab. Enter a suitable name (my one's called “Witness”) and click the Add button. You only have to do this once.

Bookmark brackets

When you replace the placeholder text it’s really easy to overtype the original bookmark and destroy it so it’s a good idea to turn on your bookmark brackets so you can see what you’re doing. For this, click File, Options, Advanced, then down to Show document content and tick the checkbox for Show bookmarks. 

Your bookmark is now enclosed in [grey square brackets]. Stay strictly inside the brackets when changing the text. To be on the safe side, I always overtype the new text from the second character and then backspace out the first character at the end. Bookmark brackets are not printed.

Inserting a REF field code

You should now insert a REF field code wherever you need to have your bookmark text copied. Click Insert tab, Text group, the Quick Parts control and then choose Field from the drop-down menu. Scroll down the list on the left, click REF and then choose your bookmark name from the Field properties list on the right. Click OK and your bookmark text should appear.

Inserting Field Codes

To see the field code, select it and press SHIFT+F9:

{ REF Witness \* MERGEFORMAT }

The \* MERGEFORMAT switch element just preserves the formatting of the field when it is updated and if that is not important to you then you can either remove it from the field code or make sure that the Preserve formatting during updates checkbox is not ticked when the code is entered. Certainly removing this switch makes your field code much easier to read and interpret, like this:

{ REF Witness }

Press SHIFT+F9 again to hide the code and then check that it’s all working. Change the bookmark text, select your field code and then press F9 to update it. When you want to update all the fields in the document press CTRL+A to select all the text and then F9 to update all fields. Your {REF Witness} code, like all field codes, has a grey background when it is active which I shall not show for the rest of this article.

No Footers on the Last Page

We need the Witness’ name and signature in the footer of every page other than the last one. On the last page there is a general declaration which is signed and dated and therefore we don't want to see the signed footer on this page. Apologies if you are a Jehovah's Witness, take pity on my pathetic sense of humour.

Signature line and Witness name appear in the footer of every page

Having a section break before the last page and then changing the footer for that section is a no good for us as it will force the signed declaration at the end of the document onto a new page whereas we need it to follow on after the last paragraph.

Except for the last page where only the page number appears

Creating Conditional Footers

The footers on each page are generated with an IF field code that uses the conditional logic, “If this page is not the last page then show the footer”. There’s one for the name and there’s one for the signature line as follows:

{IF {PAGE}<>{NUMPAGES} Signature___________}

{IF {PAGE}<>{NUMPAGES} {REF Witness}}

The PAGE field returns the current page number and the NUMPAGES field the total number of pages in the document. Therefore the expression "{PAGE}<>{NUMPAGES}" means "the number of this page is not equal to the number of the last page".

The process is as follows:

1. Edit your footer.
2. Insert the field codes.

To edit your footer click the Insert tab, Footer control and then Edit Footer. Now, press ALT+F9 to view your field codes so that you can see what you're doing.

Then click the Insert tab, Text group, the Quick Parts control and then choose Field from the drop-down menu. Scroll down the list on the left and click IF in the listing, make sure that Preserve formatting during updates checkbox is not ticked and click OK to start off your conditional field code which should be like this:

{IF } 

Again, the field code has a grey background when it's active. To continue with the rest of the field, click inside the brackets and leaving a space after the IF field insert a PAGE field, then type-in “<>” (the logical operator for "is not equal to") and then insert a NUMPAGES field. That’s the conditional test, “If the number of this page is not equal to the number of the last page?” or, in plain English, “If this is not the last page?” 

{IF {PAGE}<>{NUMPAGES}} 

The logical outcome of the test is to show the text “Signature__________” Leave a space after the NUMPAGES field and type in the text inside the closing } bracket. 

{IF {PAGE}<>{NUMPAGES} Signature___________} 

Press enter to go the next row in the footer and repeat the process for the second IF field code. In this case the logical outcome is to show the witness’ name which is done by inserting a REF field. Always try to insert your field codes using the list, don’t just type them in.

{IF {PAGE}<>{NUMPAGES} {REF Witness}}

Showing and hiding Field Codes

You don't usually need to see all the field codes in your document but you need to see them when you're working on them. Otherwise, you just let them get them get on with things. Press ALT+F9 to see all the field codes or SHIFT+F9 to see the currently selected code. Press the same shortcut keys again to hide the field codes.

Viewing the Field Codes in the page footer

Updating Field Codes 

To update all the fields in the body of your document, press CTRL+A to select and then press F9 to update. Word is divided into separate text flows or stories and your body text and your headers and footers are separate stories. CTRL+A and F9 updates all of your main story but it does not update your headers and footers story.

To update all the fields in your footers, either edit the footer and then press CTRL+A and F9 again or (far easier!) just press CTRL+F2 which switches to Print Preview and automatically updates your field codes. Then exit the preview as you usually do.

Related Posts

Word-Using Styles

Word-Typing in Accented Characters

Word-Entering Random Text

Excel Absolute References, the Dollar sign $ and the F4 key

Way back in the eighties we always used to say that if you didn't known your function keys then you weren't being serious. Thankfully those days have passed but if there's one function key you still really need to know for Excel formulas it's F4.

When you’re working in Word, Excel, or PowerPoint, the F4 key will repeat the last command or keystroke you just did. But it's primary use is for inserting the dollar signs ($) for Absolute cell references in Excel formulas.

You may have seen cell references in formulas surrounded by ‘$’ signs, for example $D$3:$D$10, and wondered what’s that all about?  The ‘$’ before the column or row reference fixes the reference so that it does not change when it's copied. You either have to type in the $ signs or press the F4 key. 

For example, looking at the table below we have a Commission Rate of 3% in cell G3. In column E we want to calculate the commission as Total x Rate @ 3%. 

Calculating the commissions at 3%

We could simply enter the formula as =D3*3% and copy it down column E, but then that gives us two major problems to deal with:

1. We can’t easily see what the commission rate is without looking in the formula bar. We could include it in the column heading as "Commission @ 3%", but that makes the heading too wide and if I should change the rate then I will have to remember to go back and change the heading as well.
2. If I do change the rate then I need to change the formula and copy it down the column again. Once would be acceptable but several times not and what if other formulas are using the commission rate? It would be so much easier just to have the commission rate in a single cell.

In the formula for the commission calculation the commission rate of 3% is entered into cell G3 and then the G3 reference is used in our formulas like this, =D3*$G$3. The reference is absolute, meaning that it never changes wherever the formula is copied.

Let’s look at what happens if we don’t use an absolute reference. If we entered in cell E3 the formula =D3*G3 we would get the correct answer. But when we copy that formula down the rest of column E Excel updates the cell references in the formula to increase by one row as we go down. You can see this to the left where the references to D3 and G3 change to D4 and G4 etc. 

These standard cell references are known as relative references. We want the D3 reference to change but we want the G3 to be fixed.

To keep the commission rate reference on cell G3 we enter the formula like this, =D3*$G$3. Then when we copy the formula down the column the column D references change but the reference to cell G3 does not.

Strictly speaking, we only needed to fix the reference to row 3 as the G column reference would not have changed but it's usually easier just to fix the entire cell reference and have done with it. The difficult bit is to realise that you needed an absolute reference in the first place.

Other ways to use absolute references

• Make a whole range of cells an absolute reference: $D$1:$F$1 
• Make only the column absolute $D3 
• Make only the row absolute D$3 

To help you see how your formulas are behaving it's quite a good idea if you can actually see them instead of the results of the formula. To display your formulas in the worksheet, click the Show Formulas control on the Formula Auditing group of the Formulas tab.

Shortcut for entering Absolute References

The F4 key instantly enters the ‘$’ signs for you. You can do it while you’re entering your formula or you can go back and edit the original formula. 

In the example below we have started to enter a formula into cell E3. We have just selected cell G3, as you can see by the marquee ("marching ants") around the cell. 

Entering the formula

At this point, before pressing ENTER or clicking the tick to finish the formula, we can press the F4 key and Excel places the ‘$’ signs around the G3 reference.

Press F4 to enter the $ signs

Or you can go back to a cell at any time. Press the F2 key to edit the cell if you are feeling old-fashioned or just double-click the cell. Click anywhere in the cell reference and press F4 to insert the $ signs. 

If you want to fix a range reference you have to highlight the cell range in the formula before pressing F4. If you keep pressing F4 Excel iterates through all the permutations of absolute and relative reference:

• With the first press of F4 you get $G$3. Column and row absolute. 
• With the second press of F4 you get G$3. Column relative and row absolute. 
• With the third press of F4 you get $G3. Column absolute and row relative. 
• With the fourth press of F4 you get G3. Column and row relative. 

Mixed Relative and Absolute references

Whilst it usually easier to fix the entire reference there are times when you must have a mixed reference, where only the row or the column is fixed, in order to make your formulas work. In the worksheet example below we are multiplying all the hundreds values in the top row against all the tens values in the first column of the table. The first formula multiplies the 10 by the 100, =B3*C2.

Multiply the row value by the column values

As the formula is copied across and down we need the row reference for the hundreds values to be fixed and the column reference for the column reference for the tens values to be fixed. But the rest of the formula must be relative so that it works correctly when it is copied.

Mixed Absolute and Relative references

Usually I try to work this type of formula out logically before I start. And usually it goes wrong so I just resort to experimentation until I get it right. It only takes me an hour or so.

Related Posts

Excel-Tick marks and Check boxes in cells

Excel-Make Pivot Tables refresh automatically

Excel-Calculations without formulas

Excel-Sorting by last name

Excel-Switching columns to rows

Excel-CTRL key tricks

Excel-Double click tricks

Excel-Converting USA dates to UK dates

Excel Top ten shortcut keys

Excel 2010 shortcut keys

Excel-AutoSum Revisited

Excel for Mac-Deleting a range and other shortcuts

Excel-Calculating age from date of birth

The NETWORKDAYS Function

Our sales rep is out Monday to Friday selling encyclopedias and he is required  to forecast his sales each day to see how he’s performing against his targets. He uses this simple formula to extrapolate his sales: =sales this month to date / workdays passed this month and the total workdays this month. The following formulas will help speed up the task for our sales rep – He might wish to get a new job as he sees digital media has taken over, and if you want to purchase an encyclopedia Amazon probably has a better deal.  NETWORKDAYS, EOMONTH and TODAY functions in Excel like this: =1000/NETWORKDAYS(EOMONTH(TODAY(),-1)+1,TODAY()-1)*NETWORKDAYS(EOMONTH(TODAY(), -1)+1,EOMONTH(TODAY(),0)) The NETWORKDAYS Function The NETWORKDAYS function returns the whole number of workdays between two dates. We can even exclude holidays. NETWORKDAYS syntax: =NETWORKDAYS(start_date, end_date, [holidays]) use the EOMONTH and TODAY functions to calculate the start_date and end_date arguments: Note: The holidays argument is optional and I haven’t used it in this example as our rep only takes weekends off! The EOMONTH function The EOMONTH function returns the serial number of the last day of the month before or after a specified number of months.(see emonth in date and times) EOMONTH syntax: =EOMONTH(start_date, months) Where ‘months’ is the specified number of months before or after the start date. You can calculate months before the start date by using a negative number as I have done above; EOMONTH(TODAY(),-1) which gives the last day of the previous month. The TODAY or NOW Function use the TODAY function to return the current date. You simply enter the TODAY function like this: =TODAY()       Date only =NOW()         Date and Time It is important that your computer time and date are correct as it will pick up the date from your computer clock. NETWORKDAYS Limitations The NETWORKDAYS function considers workdays as Monday to Friday. However in Excel 2010 a new function is included NETWORKDAYS.INTL this function allows you to enter custom weekend parameters. The syntax is:  =NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays]) as shown below.

Excel can be fun try out some of its features Detailed on this Site

Mike Barrett (MCT) Senior Training Advisor

Excel EOMONTH Function

When using Excel to produce invoices, and payments are due at the end of the month, use the EOMONTH function to automatically insert the payment due date. Excel EOMONTH Function =EOMONTH(start_date,months) Returns the serial number of the last day of the month before or after a specified number of months. How to – Calculate End of Month Date from Today =EOMONTH(NOW(),0) Use this on invoice templates where invoices are due at the end of the month. Or if your credit terms are EOM+30 days then use: =EOMONTH(NOW(),1) Note: if the date displays as a serial number simply change the formatting to a Date format. CTRL+1 to open formatting dialog box

Excel can be fun try out some of its features Detailed on this Site

Mike Barrett (MCT) Senior Training Advisor

On Time Every time

understand how Excel stores dates and time. Date and Time 101 Excel stores time and dates as serial numbers so in this case what you see is not what is in the cell. Only When you format the serial number as a date or time, or date and time as in the example below, it displays it in a date/time format but underlying is still a serial number. The serial number consists of two parts. The Serial Number The digits before the decimal comprise of  the date and the digits after the decimal make up the time. Dates in Excel Dates in Excel start from the  1st January 1900. The serial number Therefore for 1/1/1900 is 1. The serial number for 1/1/2012 is 41640 -  because it is the forty one thousand, six hundred and forty days since 31/12/1899*. Note: in the uk our dates are displayed as dd/mm/yyyy. USA mm/dd/yyyy and Germany uses the full stop instead of / or - *Actually 1/1/2012 is only the 40908 dayss but Excel includes the date 29th Feb 1900 even when the year 1900 was not a leap year. The inclusion was intentional to provide compatibility with Lotus 1-2-3 which contained a bug and was the market leader when Excel was released! Time in Excel Time serial numbers represent a fraction of a 24 hour day. Convert Time to Decimals we need to convert time to a decimal on a regular basis so that we can calculate hours x rate for the purpose of payroll or billing. Remember, the serial number is a fraction of a day so simply multiply by 24. Note: you don’t have to enter a date and time. If you enter time only, the date part of the serial number is 0. We have the rules let’s work on some examples working with time. Shift Work Timesheets and Overtime Calculating the difference between two times on the same date is as simple as subtracting the start time from the finish time, but it’s not so easy if your start and finish times are on different dates, as in the case of shift workers. Notice the finish time below for Monday is actually 7AM on Tuesday. We can use a clever trick to test for time that finishes on a different date by checking whether the finish time is less than the start time, as is the case for Monday and Tuesday above. Taking the formula in cell G4: The first part of the formula takes the finish time less the start time and then checks whether the finish time is less than the start time (E4<B4). In the case of Monday (E4<B4) evaluates to TRUE, and since TRUE = 1 it adds 1 to E4-B4 to correctly calculate the time. Note: if your times are entered with the date and time you can simply subtract one from the other, it’s only in the case where times are entered on their own that you need to test whether the finish time is < the start time. Simple Time Sheet Type the week start date in cell C3, the Week beginning. Use the format dd/mm/yy, the name of the day will appear automatically. The date is then passed down to the Day column. Type the amount of hours you are expected to work in G3, the Normal Hours. This is used later to calculate if have worked over or under the required hours. Type the times you arrive and leave work in the appropriate columns. Use the format of hh:mm. Heres The Formulas revealed Note The Total Hours cell has been formatted as [hh]:mm. This ensures the total hours can be expressed as a value above 24 hours. If the [hh]:mm format had not been used the Total Hours would show as : 14:45 If the [hh]:mm format does not show in the cell format dialog box on your computer, it can be created using Format, Cells, Number, Custom.     Rounding Time Often solicitors need to round time in 10 minute increments for the purpose of billing clients at an hourly rate. The table below shows rounding using the ROUNDUP, MROUND and ROUND functions. If you want to bill in 30 minute increments change the 10 in the above formulas to 30. Display Time with Text How to display time in a format that reads ‘2 hours 15 minutes’. There are quite a few formulas that will concatenate text for the words ‘hours’ and ‘minutes’, but I prefer to simply use a custom number format. You can see in cell K2 the formula subtracts the start time from the arrival time to give the number of hours late. I then formatted the cell to show the time with words using a custom number format like this: The benefit of this approach is that the underlying time value remains in the cell so you can use it in other formulas. For example you might like to add up the Time Late column to get a total time late etc

Excel can be fun try out some of its features Detailed on this Site

Mike Barrett (MCT) Senior Training Advisor

Datedif in Depth

Your required to calculate the number of months between two dates? You’ll be needing the Hidden DATEDIF function. Try typing =DATEDIF into Excel and it will pretend it doesn’t recognise it – see how it’s not in the list of functions to the left unless you type it all in. Though a common feature used in Microsoft Access For some reason Microsoft don’t think the DATEDIF function is worthy of any documentation since Excel 2000. In fact they say they only include it in recent versions for backward compatibility. The DATEDIF function calculates the difference between two dates. DATEDIF syntax =DATEDIF(date1,date2,interval) Date1 is the start date , Date2 is the end date, Interval is the type of interval you want to calculate e.g. days, months, years. DATEDIF Interval Formats: ym = Complete calendar months between two dates as though the end date is in the same year as the start date. yd = Complete calendar days between two dates as though the end date is in the same year as the start date. y = years. Complete calendar years between two dates. md = complete calendar days between two dates as though the month and year of the end date is the same as the start date. m = months. Complete calendar months between two dates. d = days. Complete days between two dates. Let’s examine some DATEDIF examples: Note: you could achieve the same result for example 1 above with this formula: =C4-B4 Ok, so the above are some pretty basic calculations. Example 1: The number of Years since The Queen ascended the throne: =DATEDIF("6/2/1952",TODAY(),"Y") Value Returned= 61 Note: in the above formula TODAY() = 6th may 2013. The TODAY() function will return today’s date as per your computer clock. Also handy for report headers and the like. Example  2: The Queens age today (6th may 2013) in days, months and years: =DATEDIF("21/04/1926",TODAY(),"y")&" years,"&DATEDIF("21/04/1926",TODAY(),"ym") &" month(s), "&DATEDIF("21/04/1926",TODAY(),"md")&" Days" Value Returned=87 years,0 month(s), 15 Days The above formula uses the concatenation method (see Text Functions). Example 3: Calculate how long untill my next holiday. Supressing 0 values where there are no years or months returned: ="I Go On Holiday in "&IF(DATEDIF(TODAY(),"17/03/2014","y")=0,"",DATEDIF(TODAY(),"17/03/2013","y")&" years ")&IF(DATEDIF(TODAY(),"17/03/2014","ym")=0,"",DATEDIF(TODAY(),"17/03/2014","ym")&" months ")&DATEDIF(TODAY(),"17/03/2014","md")&" days" Value Returned= I Go On Holiday in 10 months 11 days the above formula will work as long as the date 17-03-14 is not exceeded We use the IF function to first evaluate whether there are any years to count, if not return nothing (as stipulated by the empty double quotes “”), and so on for months and days. Ideas for Using DATEDIF: Your age in years, months and days. Age is 51 Years, 7 Months and 5 Days And the formula ="Age is "&DATEDIF(C8,TODAY(),"y")&" Years, "&DATEDIF(C8,TODAY(),"ym")&" Months and "&DATEDIF(C8,TODAY(),"md")&" Days" Using the layout above and changing the labels try out the ideas below. Length of service of an employee. Equipment age. Countdown to a date. DATEDIF Errors Returned If Date 1 is later than Date 2 Excel will return a #NUM error. If Date 1 or Date 2 is not a valid date you will get a #VALUE error. If the interval is not one of the above options Excel will return a #NUM error.

Excel can be fun try out some of its features Detailed on this Site

Mike Barrett (MCT) Senior Training Advisor

Excel EDATE Function

To calculate due dates for projects or payments then Excel’s EDATE function will make life easier for you. Excel’s EDATE function is ideal for: calculating when a payment is due or project is to be completed calculating the date a scheduled number of months in the past or into the future Excel EDATE Function Syntax =EDATE(start_date,months) Returns the serial number of the date that is the indicated number of months before or after the start date. Calculate Date from Today 3 Months from today: =EDATE(NOW(),3) 3 Months prior to today: =EDATE(NOW(),-3) Note: if the date displays as a serial number simply change the formatting to a Date format. CTRL+1 to open formatting dialog box

Excel can be fun try out some of its features Detailed on this Site

Mike Barrett (MCT) Senior Training Advisor

Excel TIME Function Explained

Here’s a problem for you. You want to find out the time in another time zone.  Let’s say New York. Your local time in sunny London, UK right now is 4:00 AM (the sun is just coming up). You know your local time is GMT+ 0, and you know that the time in New York is GMT-5. This is starting to sound like one of those maths questions you used to get in school. You know the ones that go something like: “Michael is two years older than three times Deborah's age. If Deborah is ‘x’ years old, how would you calculate Michael’s age?” Don’t worry I wouldn’t do that to you. My question is much simpler…well maybe: What formula would you use in Excel to calculate the local time in New York if it’s 4:00 AM in London, UK? Lets show you how to do it: The calculation you want to do is: =4:00AM  –  (0hrs + 5hrs) And the answer is 1:00 PM. So easy to work out on paper but not that straight forward when using excel. Calculating Time in Excel Known Problems Excel can’t handle negative times.  So taking the example above your formula would result in a negative time and all you’d get is ######. When you enter time in Excel you need to enter it in h:mm:ss format. Simply entering ‘10’ for 10 hours will confuse Excel. My resoloution: Row 4 shows components of my calculation, and row 5 shows the behind the scenes view (from the formula bar) of what I actually typed in the cells above. Remember British summer time not allowing for that will give you the wrong result. Cell B4 – For Excel to calculate a negative time you actually need to also give that time a date. It can be any date because you’ll format the cell to h:mm AM/PM and the date won’t be visible anyway. To be exact type =now() Cells C4 and D4 – I’ve used the TIME function here. The syntax for the TIME function is: =TIME(hour,minute,second)  Notice in my example I only have hours and so I’ve left the minutes and seconds blank. Cell E4 – this contains my formula =B4-(C4+D4) The example above only works with positive numbers the example below handels both positive and negative time zones. Alternative Method incorporate a VLOOKUP table that lists all of the Time Zones. insert drop down lists to select the time zone from my table with a named range of time_zone. (see data validation) And then I used a VLOOKUP formula in column E like this one in cell E13: =TIME((HOUR(B13))+VLOOKUP(D13,zone,2,FALSE),MINUTE(B13),) Of remember if you only have one time zone that you’re constantly converting to then you can add a time zone to your Outlook calendar and see your local time and other time zone side by side.

Excel can be fun try out some of its features Detailed on this Site

Mike Barrett (MCT) Senior Training Advisor

SUMPRODUCT Function an Alternative to SUMIFS

Excel’s SUMPRODUCT function for Excel 2003 users who require the SUMIFS, COUNTIFS or AVERAGEIFS functions (the *IFS series of functions).found in office 2007 onwards. Excel 2007 or 2010 users keep we use SUMPRODUCT which gets around the limitations of  the Excel’s *IFS series of functions. Limitations using the *IFS functions: The *IFS functions works only with the AND logic between the criteria. Example: SUMIFS(sum the range A if range B = criteria 1, AND range C = criteria 2, AND range D = criteria 3…..) However using  SUMPRODUCT we can utilise the OR logic as well as the AND logic in a SUMIF style of calculation. Example: SUMPRODUCT(range A, if range B = criteria 1, OR range B = criteria 2, AND range C = criteria 3…..) Note: the configuration of the SUMPRODUCT formula above is for illustration purposes only, the actual syntax of the SUMPRODUCT function is different. See below. The Excel SUMPRODUCT Function using examples The data below is laid out in a table that has been converted to a range. Each column has a named range the same as in the header row 1. In my examples I will refer to the column range G2:G207 as the named range ‘Applied’. Using the SUMPRODUCT function Instead of SUMIF Using the data above let’s say I want to sum the Volume for the Endrulf solar system. My SUMPRODUCT formula would look like this: =SUMPRODUCT((Volume)*(solarSystem=”Endrulf”)) How to use the SUMPRODUCT function Instead of SUMIFs You are required to sum the volume for Endrulf solar system AND IF Jumps = 6 =SUMPRODUCT((Volume)*((solarSystem=”Endrulf”)*(jumps=6))) How to use the SUMPRODUCT to SUMIF with OR as well as AND logic =SUMPRODUCT((Volume)*((solarSystem=”Rens”)+(solarSystem=”Endrulf”))*(jumps=6)) How the  formula reads: =SUM ((Volume) IF ((solarSystem=”Rens”) OR (solarSystem=”Endrulf”)) AND (jumps=6)) In SUMPRODUCT functions you can employ the AND logic, and OR logic using the * and + symbol: When the multiplication symbol * is used it reads ‘AND’. When the plus symbol + is used it reads ‘OR’. How the SUMPRODUCT Function Works In the SUMPRODUCT function Excel is testing for TRUE or FALSE answers, and in Excel the numeric equivalent for TRUE is 1, and for FALSE it is 0. These are standard Boolean terms…. So, using this formula: =SUMPRODUCT((Volume)*((solarSystem=”Rens”)+(solarSystem=”Endrulf”))*(jumps=6)) Let’s look at our data and take row 4 below as an example and apply the formula: = 13,417 * 1 * 0 = 0 Function Explained: SUM Volume 13,417 * 1 (because G4=Rens therefore = TRUE, which = 1) * 0 (because H4=7 therefore = FALSE which = 0) Or if we look at row 6: 5,217,955 * 1 * 1 = 5,217,955 How to use the SUMPRODUCT to COUNT with multiple criterion Using the example; if you want to use SUMPRODUCT to count values based on multiple criterion using AND or OR, you would simply drop the Volume component of the formula like this: =SUMPRODUCT(((solarSystem=”Rens”)+(solarSystem=”Endrulf”))*(jumps=6)) How to use SUMPRODUCT to AVERAGE with multiple criterion To calculate the AVERAGE we simply divide the total amount by the COUNT of the total volume like this: =SUMPRODUCT((Volume)*((solarSystem=”Rens”)+(solarSystem=”Endrulf”))*(jumps=6)) / SUMPRODUCT(((solarSystem=”Rens”)+(solarSystem=”Endrulf”))*(jumps=6)) SUMPRODUCT Using Dates as Criteria Now we shall add the criteria for the month of January 2011 instead of the ‘jumps=6’: =SUMPRODUCT((Volume)*((solarSystem=”Rens”)+(solarSystem=”Endrulf”))*(Date>=DATEVALUE(“01/01/2011″)*(Date<=DATEVALUE(“31/01/2011″)))) Remember the Date could also refer to a cell that contained the date, or the date serial number: Using cell references for the date (cell L12 contains 1/1/2011 and cell M12 contains 31/1/2011): =SUMPRODUCT((Volume)*((solarSystem=”Rens”)+(solarSystem=”Endrulf”))*(Date>=L12)*(Date<=M12)) Using serial numbers for the date: =SUMPRODUCT((Volume)*((solarSystem=”Rens”)+(solarSystem=”Endrulf”))*(Date>=40544)*(Date<=40574)) SUMPRODUCT as an Alternative to Helper Columns What say we wanted to know the sum of the Volume x Price. We could insert a formula in column J that calculated Price x Volume for each row of data, and then sum column J to get a total, or we could use the SUMPRODUCT function like this: =SUMPRODUCT(price,Volume) Remember: ‘price’ is the named range for column A and ‘Volume’ is the named range for column D. The advantage of the SUMPRODUCT of using  this calculation is that you can achieve the same result in one cell that would otherwise take up whole columns. Recap the Rules In SUMPRODUCT functions you can use  the AND logic, and OR logic using the * and + symbol: When the multiplication symbol * is used it reads ‘AND’. When the plus symbol + is used it reads ‘OR’. Tip: if your formula results in a zero and you know it should be >zero then you either have an error in your data, or you have an error in your formula.

Excel can be fun try out some of its features Detailed on this Site

Mike Barrett (MCT) Senior Training Advisor

Excel Custom Lists

Type a day of the week or a month of the year into an Excel cell and you can drag the Fill handle (the little black cross) at the lower right corner to complete the sequence. 

Very handy when you need it but an inconvenience when you don't. How do you copy the word "March" as a constant when you have a list of events all happening in March? The answer is to hold down the CTRL key as you drag. A small plus sign appears on the Fill handle and the Autofill sequence is not generated.

Both the days of the week and months of the year are standard Excel Custom Lists but I think that these lists become really invaluable when you start to create your own sequences. Your Custom lists are stored with your copy of Excel so that once you have set them up you can use them on any workbook. For many of us so much spreadsheet work consists of the sheer drudgery of data entry and creating a few common lists like lists of department names, product names etc. makes a world of difference and cuts down the eternal copying and pasting of data.

Creating Custom Lists

You need the the Custom Lists dialog to create your list but opening it can be a bit of a struggle and depends on which version of Excel you have. If you've got the good old-fashioned classic version of Excel with the drop down menus then it's Tools, Options and look for the Custom Lists tab.

For Excel 2007 click the Office button then Excel Options and the Edit Custom Lists command button is in the first section.

From Excel 2010 onwards Custom Lists has been wrapped up in a bag, the bag left in a drawer and the chest of drawers moved to the spare room. Where have they put it? 

Easy! It's File tab, Options, Advanced then scroll way down the list until you find the General section and it's the Edit Custom Lists command button over to the right. Usually quite invisible at this stage as your eyes are drawn to the left after scrolling down that huge list of sections. Often you can't see the Edit Custom Lists button for looking—only the other day I spent a good ten seconds staring at it before my brain cell said "Yeah!"

There it is!

After this treasure hunt actually creating the list is a breeze, type the entries into the List entries box on the right hand side separating each entry either with a comma or by pressing the Enter key and when you have finished click the Add button. Your new list appears in the listing on the left. You can delete the custom lists you have created yourself but you can’t delete the preset day and month lists.

Importing Custom Lists

But it is usually much easier to import your list from some cells, after all that's why we're doing this in the first place; we don't want to have to keep typing in our list. There's a very good chance that you already have your list entered somewhere so select the cells first and then click Import when you open the dialog or click the red arrow to collapse the Import list from cells box.

Importing a Custom List

Using your Custom List

Once your list has been created you use it exactly as you do the days and months lists—type in one of the entries from the list, then grab the Fill handle and drag to generate the rest of the sequence.

Don't forget that you can also use your Custom list for sorting, Excel will treat the items in the list as the most important things in that order when sorting, with any items not in the list automatically going in alphabetical order at the end of the data. This way, you could create a list of the top 10 clients who would always appear in the order you would like at the top of the data and all others would appear in normal alphabetical order below the top 10.

Sort by Custom List

Related Posts

Excel-Tick marks and Check boxes in cells

Excel-Make Pivot Tables refresh automatically

Excel-Calculations without formulas

Excel-Sorting by last name

Excel-Switching columns to rows

Excel-CTRL key tricks

Excel-Double click tricks

Excel-Converting USA dates to UK dates

Excel Top ten shortcut keys

Excel 2010 shortcut keys

Excel-AutoSum Revisited

Excel for Mac-Deleting a range and other shortcuts

Excel-Calculating age from date of birth

Excel Slicers

Excel Slicers

Excel Slicers are a fun, friendly and powerful way to filter  the data displayed for Pivot Tables, Pivot Charts, Power Pivot Tables and (for Excel 2013) normal Excel Tables.

These visual filters are an alternative to the field drop down lists and one Slicer can be connected to several  different Pivot Tables or Charts to instantly apply a common filter. 

Inserting Slicers

To insert a Slicer select a cell in the Table or PivotTable that you want the Slicer to control and then click the Insert tab of the ribbon, the Slicer control is in the Filters group. In Excel 2013 you can also right-click on the Field in the PivotTable Field List and choose Add as Slicer from the shortcut menu.

Inserting a Slicer

Connecting Slicers to Multiple Pivot Tables and Charts

Once you’ve inserted your Slicer you can then choose which Pivot Tables or Charts you want it to control. Right-click the Slicer and choose Report Connections from the short cut menu. This opens the dialog box where you can tick the boxes for the Pivot Tables you want your Slicer to control.

Multiple Connections

A Slicer can only control Pivot Tables which share the same Pivot Cache. Typically Pivot Tables which reference the same data source share a Pivot Cache, but this is not always the case.

If the Pivot Tables that you want to connect to don’t appear in the list then it is because they have separate Pivot Caches.

Filtering with Slicers

Slicers are easy to use and allow us to filter one or more items.

• Click one item to apply the filter for that area. 
• Click and drag through items to select more than one or click the first item, hold down the SHIFT key and click on the last item in your range. 
• Hold down the CTRL while clicking to select multiple items.

The Slicer displays the selected items in a different colour to give a visual indication of the applied filter. To remove all filters, click the red X at the top right corner of the Slicer.

Multiple filters

Slicer Settings

Click the outer edge of the Slicer to display the Slicer Tools, Options tab on the ribbon. Here we can access all the Slicer Settings.

Slicer Settings

1. Change the Slicer name
2. Turn the Slicer header on or off, or change the caption.
3. Sorting settings.
4. Choose how the Slicer should handle items with no data.

Slicer Styles

In the Slicer Styles group we can choose the colour and style, the colour options will be based on the Theme that you have selected for the workbook in the Page Layout tab of the ribbon.

Slicer Styles

Buttons and Size

Use the Buttons and Size groups to set the number of columns, button height and width and the overall size of the slicer. You can also size the Slicer by grabbing and dragging the selection handles on its outer edge. 

Sizing Slicers

If the overall height of your Slicer is too small to show all of the filter values then a scroll bar is displayed.

Slicer scroll bar

Formatting Slicers

Slicer Styles

You can choose a design from the Slicer Styles gallery or create your own Slicer style. Click on the down arrow to expand the gallery and click New Slicer Style.

This opens the dialog where you can format each of the 10 Slicer elements exactly as you want. You can click the check box to Set as default slicer style for this document to use the style over and over again.

Copy and Modify Slicers

Duplicate the Slicer style

You may prefer to copy a Slicer that has the colours you want to use and then just modify the fonts, borders and fill colour.

To copy a style right-click the style in the gallery and click Duplicate. This opens the Modify Slicer Style dialog.

Multiple Slicers

After duplicating your Slicer style you can apply it to your Slicers. If you have multiple Slicers you can select them by holding down the CTRL key as you click each one. Then click on your new style in the Slicer Style gallery.

Related Posts

Excel-Make Pivot Tables refresh automatically

Excel-Calculations without formulas

Excel-Sorting by last name

Excel-Switching columns to rows

Excel-CTRL key tricks

Excel-Double click tricks

Excel-Converting USA dates to UK dates

Excel Top ten shortcut keys

Excel 2010 shortcut keys

Excel-AutoSum Revisited

Excel for Mac-Deleting a range and other shortcuts

Excel-Calculating age from date of birth