All articles with 'date and time' Tag

Rounding time to nearest minute or quarter hour etc. [formulas]

Published on Jun 26, 2017 in Excel Howtos
Rounding time to nearest minute or quarter hour etc. [formulas]

The other day, I was building a spreadsheet to calculate FTE (full time equivalent) for staff based on hours worked on various days in a fortnight. While building the spreadsheet, I came across an interesting problem. Rounding Time to nearest minute.  We can’t use ROUND() or MROUND() to round time as these formulas aren’t designed to work with time values. Although time values are technically decimal, rounding time to nearest minute (or quarter hour etc.) can be tricky when usual round formulas. Let me share a few formulas to round time to nearest point.

Let’s say you have a time value (either user input or calculated) in cell A1.

Use below formulas to round time in A1.

Continue »

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 »

Add any number of days, months or years to a date with this simple trick

Published on Aug 2, 2016 in Excel Howtos, Learn Excel
Add any number of days, months or years to a date with this simple trick

Let’s say you have a date in A1 and want to find out future date after 2 years, 4 months and 9 days.

Here are a few formulas you can try.

  1. =A1 + DATE(2,4,9)
  2. =EDATE(A1, 2*12+4) + 9
  3. =A1 + 2*365 + 4*30 + 9

Surprisingly, each formula gives a different result! So which one should you use?

Continue »

How many ‘Friday the 13th’s are in this year? [Formula fun + challenge]

Published on May 13, 2016 in Excel Howtos, Learn Excel
How many ‘Friday the 13th’s are in this year? [Formula fun + challenge]

Today is Friday the 13th. If you are a raging friggatriskaidekaphobiac, I suggest you to stop reading this post. For the rest of you, I have something fun.

Given a year in cell C3, let’s find out all the months with Friday the 13th. Something like above.

Continue »

Figure out slot from given time [quick tip]

Published on Apr 19, 2016 in Excel Howtos, Quick Tip
Figure out slot from given time [quick tip]

Here is an interesting scenario.

Let’s say you are looking at a time, like 9:42 AM and want to know which 15 minute slot it fits into. The answer is 9:30 – 9:45. But how would you get this answer thru Excel formulas?

Continue »

How many Mondays between two dates? [homework]

Published on Dec 18, 2015 in Excel Challenges
How many Mondays between two dates? [homework]

Here is a quick but challenging homework problem for you.

Let’s say you have two dates – Start and End.

And you want to find out how many Mondays are there between those two dates (including the start & end dates).

What formula would give the answer?

Please post your formulas / VBA functions / DAX measures in the comments section.

Continue »

How to highlight overdue items [video]

Published on Aug 3, 2015 in Learn Excel
How to highlight overdue items [video]

We, adults can’t escape three things:

  1. Deadlines
  2. Demanding bosses (replace with customers or nagging spouses or naughty kids)
  3. Taxes

While I can’t help you with demanding bosses or taxes, when it comes to deadlines, I have the right tool for you.

A tracker that highlights all overdue items so that you know where to focus your attention.

Let’s learn how to use awesome powers of Excel to find-out which items are due. You can apply these concepts to nail down over due invoices, pending project tasks or scheduling workforce.

Continue »

Calculating Billy’s total working hours [solution & discussion]

Published on Jun 22, 2015 in Excel Challenges

Few days ago, I asked you “How many hours did Billy work?” There were more than 100 responses with lots of innovative solutions.

So today, let’s examine various ways to calculate total working hours given start & end times of tasks. Please watch below video.

Calculating Bill’s total working hours (video)

Continue »

How many hours did Billy work? [Solve this]

Published on Jun 5, 2015 in Excel Challenges

Here is a simple but tricky problem. Imagine you are the HR manager of a teeny-tiny manufacturing company. As your company is small, you just have one employee in the shop floor. He is Mr. Billy. As this is a one person production facility, Billy has the flexibility to choose his working hours. At the […]

Continue »

Convert fractional Excel time to hours & minutes [Quick tip]

Published on Aug 19, 2014 in Excel Howtos
Convert fractional Excel time to hours & minutes [Quick tip]

Time for another quick Excel tip.

Lets say the park near your house rents tennis courts by hour. And they charge $10 per hour. At the end of an intense tennis playing week, Linda, the tennis court manager called you up and said you need to pay $78 as rent for that week.

How many hours did you play?

Of course 78/10 = 7.8 hours.

But we all know that 7.8 hours makes no sense.

We also know that 7.8 hours is really 7 hours 48 minutes.

So how to convert 7.8 hrs to 7:48 ?

Continue »

Building a simple timer using Excel VBA to track my Rubik’s cube solving speed [case study]

Published on May 13, 2014 in VBA Macros
Building a simple timer using Excel VBA to track my Rubik’s cube solving speed [case study]

Today, lets learn how to make a simple timer app using Excel. First some background…,

Recently, I learned how to solve Rubik’s cube from my nephew. As a budding cuber, I wanted to track my progress. Initially I used the stopwatch in my iPhone. But it wont let me track previous times. So I thought, “Well, I can use Excel for this”.

So I made a small timer app using Excel. Its quite minimalistic. It has a single button. I press it and it tracks the start time (date & time stamp). If I press the button again, it records the duration.

This way, I can see my progress over next few weeks and may be plot the trend.

Continue »

Free 2014 Calendar, daily planner templates [download]

Published on Jan 2, 2014 in Learn Excel
Free 2014 Calendar, daily planner templates [download]

Here is a New year gift to all our readers – free 2014 Excel Calendar & daily planner Template.

This calender has,

  • One page full calendar with notes, in 4 different color schemes
  • Daily event planner & tracker
  • 1 Mini calendar
  • Monthly calendar (prints to 12 pages)
  • Works for any year, just change year in Full tab.
Continue »

Find last day of any month with this simple trick [formulas]

Published on Nov 21, 2013 in Excel Howtos
Find last day of any month with this simple trick [formulas]

Here is a handy trick to calculate last day of any month.

Assuming y and m contain the year & month for which you want to find the last day’s date, write

=DATE(y, m+1,0)

That is right, you can use ZERO (0) as the day.

When you do this, Excel tells us the last day of previous month.

Continue »

42 tips for Excel time travelers

Published on Oct 17, 2013 in Excel Howtos
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.

Continue »

Sort by Birthday [Quick tip]

Published on Aug 26, 2013 in Excel Howtos
Sort by Birthday [Quick tip]

Lets say you have a list of employees and their birthdays. Now you want to sort this list, based on their birthday, not age. How would you do it?

Continue »