All articles with 'between formula' Tag

How many employees are on leave during Easter holidays [Homework]

Published on Apr 7, 2017 in Excel Challenges
How many employees are on leave during Easter holidays [Homework]

Easter is around the corner. After what seemed like weeks of lousy weather, finally the sun shone today. I capitalized on the day by skipping work, walking kids to school, taking Jo out for some shopping, enjoying a leisurely walk / cycling with Nishanth in the park and almost forgetting about the blog. But it is dark now and before tucking the kids in, let me post a short but interesting home work problem.

Let’s say you are HR manager at Egg Co. and you are looking at the vacation plans of your team.

Easter is your busiest time and it would be a bummer if a majority of your staff are on leave during the Easter season (14th of April to 28th of April, 2017). So you want to know how many people are on leave. A snapshot of your data (table name: lvs) is shown above.

Click here to download the sample file.

You want to answer below three questions:

  1. How many employees are on leave during Easter holidays (14th of April to 28th of April)?
  2. How many employees are on approved vacation during Easter holidays?
  3. How many employees in “Team ninja” are on approved leave during Easter holidays? Assume team employee numbers are in named range ninja
Continue »

Write a formula to check if two dates are in same month? [homework]

Published on Nov 2, 2012 in Excel Challenges, Excel Howtos
Write a formula to check if two dates are in same month? [homework]

Its Home work time folks. Sharpen your Excel pencils and get cracking.

Find out if 2 dates are in same month

Lets say you have 2 dates in A1, A2.

Q1. What formula tells us if both of them are in same month?

Both dates must be in same month & year!

Go ahead and post your answer in comments.

Continue »

Find-out Days Overlaped [Excel Formula Homework]

Published on Jul 7, 2010 in Featured, Learn Excel
Find-out Days Overlaped [Excel Formula Homework]

Few days back we have learned how to find if 2 sets of dates overlap using Excel Formulas. For eg. we have learned that to check whether {1 July,2010 to 23 July 2010} overlaps with the date range {15 July, 2010 to 16 August 2010}, we can use a formula like, =if(or(and(x>=a,x<=b),and(a>=x,a<=y)), “Overlap”,”Do not overlap”) […]

Continue »

Range Lookup in Excel [Formulas]

Published on Jun 30, 2010 in Learn Excel
Range Lookup in Excel [Formulas]

Here is a really tricky problem. Recently I was given a data set like this (shown below) and asked to find the position of lookup value in the list. The only glitch is that, instead of values, the lookup table contained lower and upper boundaries of the values. See the below illustration to understand what […]

Continue »

Between Formula in Excel [Quick Tips]

Published on Jun 24, 2010 in Excel Howtos
Between Formula in Excel [Quick Tips]

Checking if a value falls between 2 other values is fairly common when you are working with data. In today’s quick tip, we are going to learn how to check for such between conditions in excel. You will be learning how to check if a value, date or text falls between 2 other values, dates or texts (when arranged in dictionary order) as shown in the picture aside.

Continue »

Check if two ranges of dates overlap [Excel Formulas]

Published on Jun 1, 2010 in Learn Excel
Check if two ranges of dates overlap [Excel Formulas]

While preparing a project plan, I had a strange problem. I wanted to highlight all the project tasks that fall with-in a certain date range. At the lowest level, the problem is like this: There are 2 ranges of dates (a,b) and (x,y) and I want to know if they overlap (ie at least one […]

Continue »