Count Activities in a Date Range

Today's challenge is to count how many guests stayed at a hotel, in a specific date range, based on the guest arrival and departure dates.

Find Specific Dates

In previous examples, we've seen how to check if a specific date falls within a date range. For example, if you have a list of orders, you can use SUMIF or SUMIFS, to add up all the orders between a start and end date. You can see the written instructions for this on my website.

Guest Visits

It is a little trickier to count hotel guests though, because the

Continue reading Count Activities in a Date Range

Excel Roundup 20141215

Weekly Excel Roundup http://blog.contextures.com/

Thank you for reading my Excel Roundup every Monday, and after this week, I will be taking a couple of weeks off, to enjoy the holidays. There will be a blog post here this Thursday, and the next roundup will be posted on January 5th.

To help you celebrate, here is a no-macro animated Christmas tree, created in Excel, of course. Just move the scroll bar, to add lights, tinsel, a star, and presents, to the tree. You could use this technique for less festive projects too, like a business dashboard. Read the details on my Contextures website.

 

Continue reading Excel Roundup 20141215

Select Multiple Items From Long List in Excel

Select multiple items with combo box and listbox http://blog.contextures.com/

It's hard enough to select one item from a long drop down list – how can we make it easier to select multiple items for a cell?

In the screen shot below, the worksheet has code that lets you select multiple item from the drop down list. Each new item is added to the cell, instead of replacing the previous selection.

Continue reading Select Multiple Items From Long List in Excel

Excel Roundup 20141208

Christmas is only 2-1/2 weeks away, so it's time to start planning! Of course you'll want to organize everything in a spreadsheet, so you can download the free Excel Holiday Planner, from my Contextures website.

It has shopping lists, budget sheets, task lists, and even a dinner planner, so get your copy, and get the holidays under control.

Continue reading Excel Roundup 20141208

Show Drop Down List With Specific Letters

drop down list with specific text http://blog.contextures.com/

If you've got a long list of items, it can take a while to find what you're looking for, in a data validation drop down list.

For example, in the screen shot below, you'd have to scroll down to the bottom of this long list, if you want to select the Teatime Chocolate Biscuits (and don't pick the Tofu by mistake!)

Continue reading Show Drop Down List With Specific Letters

Contextures Cyber Monday Sale 2014

Happy Cyber Monday! I hope you didn't spend all you money at the Black Friday sales, and have a little left over for today's shopping spree.

To celebrate Cyber Monday, the busiest online shopping day of the year in the USA, you can buy a couple of my Excel products, at 50% off today only. The details, and links to the product pages, are below.

NOTE: Unfortunately, the shopping cart will only discount one item per order -- if you want both items, please order them separately.

Continue reading Contextures Cyber Monday Sale 2014

Excel Roundup 20141201

Weekly Excel Roundup http://blog.contextures.com/

Wow! It's December already – how did that happen? It's Cyber Monday too – a big online shopping day. Did you find any Excel bargains?

To start the countdown to Christmas, Mynda Treacy created an Advent Calendar in Excel. You can download the file, if you'd like a bit of fun while you count the days until Christmas

You'll have to answer a quiz each day, to open the calendar doors. To get a head start, you can start thinking about the answer to the December 5th question, shown in the screen shot below.

Continue reading Excel Roundup 20141201

Contextures Black Friday Sale 2014

Happy Black Friday! If you celebrated Thanksgiving yesterday, I hope you enjoyed time with your friends and family, and are ready to do some shopping today.

To celebrate Black Friday, the busiest shopping day of the year in the USA, you can buy my popular ebook kit – Excel UserForms for Data Entry – at 50% off today. So, instead of the regular price of $30 US, you can get the kit for only $15 US. The price will be reduced automatically – you don't need to enter a coupon code.

The special price ends tonight (Nov. 28, 2014) at

Continue reading Contextures Black Friday Sale 2014

Convert Currency With Different Separators

change currency separators http://www.contextures.com/xlDataEntry03.html#diffsep

Happy Thanksgiving, if you're celebrating today. Tomorrow is Black Friday – the biggest shopping day of the year, in the USA. Up here in Canada, we celebrate the day too, so I'll put one of my Excel products on sale for the day. Remember to check this blog tomorrow, to get the coupon code.

If you're doing your shopping around the world, you might end up with a worksheet filled with amounts in a different currency. For example,

In the USA, a comma is the Thousands separator, and a period is the Decimal separator -- 987,654.32 In Germany, a period

Continue reading Convert Currency With Different Separators

Excel Roundup 20141124

Weekly Excel Roundup http://blog.contextures.com/

In a short video, Sara Silverstein shows 6 keyboard shortcuts for formatting numbers in Excel. Do you use any of these?

You can see the shortcuts in the video below, or on the Business Inside website.

Continue reading Excel Roundup 20141124