Search

# All articles with 'date and time' Tag

## How to use Date & Time values in Excel – 10 + 3 tips

Published on Jul 28, 2021 in Excel Howtos, Learn Excel

Excel date time features are very handy and knowing how to use Excel date values can help you save a ton of time in your day to day spreadsheet chores. Let us prepare for your date with the sheet using these 10 handy tips.

Before jumping on to the tips, it helps to know how excel represents the date and time.

Microsoft Excel stores dates as sequential numbers … January 1, 1900 is serial number 1, and 28 July, 2021 is serial number 44405 because it is 44,405 days after January 1, 1900. Excel stores times as decimal fractions because time is considered a portion of a day.

Continue »

## Top 10 Excel formulas for IT people

Published on Jun 18, 2020 in Learn Excel, Project Management

Are you in IT & use Excel often? This article explains top 10 formulas for IT professionals. Useful for project managers, IT analysts, Testing people and BAs.

We cover a 10 practical situations and explore various Excel formulas to solve them. Example workbook provides more details too.

Continue »

## Highlight due dates in Excel – Show items due, overdue and completed in different colors

Published on May 18, 2020 in Excel Howtos, Learn Excel

Congratulations to you if your job does not involve dead lines. For the rest of us, deadlines are the sole motivation for working (barring free internet & the coffee machine in 2nd floor, of course). So today, lets talk about a very familiar problem.

How to highlight due dates in Excel?

The item can be an invoice, a to do activity, a project or anything. So how would you do it using Excel?

Continue »

## When is the next Monday? [Homework]

Published on Aug 31, 2018 in Excel Challenges

Psst. Hey you, yeah, you there… have time for a quick date? A date with Excel that is.

Assuming you have an input date in cell A1,

• What is the formula for finding next Monday?
• What is the formula for finding first Monday of next month?

Continue »

## How much long service bonus to pay? [Homework]

Published on Dec 8, 2017 in Formula Challenges

‘Tis Friday and it is too hot in my home office to stand and type a longish post. So, let’s keep this skirtish (short and pretty).

How would you calculate long service bonus?

Let’s say you are HR manager at BigLargeInc. and you are looking at Julia’s service details. You have her employment start date, current date, her leave without pay details, as shown below.

You need to calculate how many days of continuous service Julia had (ie total service – duration on leave without pay). How would you write the formula?

Continue »

## Filter all records for November or 11AM or 2017 [quick tip]

Published on Dec 4, 2017 in Quick Tip

Imagine you are the first officer at ship terminal αε974F1 on remote planet Alderaan. Your job involves looking at terminal log to see anomalies in time space continuum. So one day after getting to work late, thanks to crazy traffic on the floating super way in your settlement, you are looking at latest terminal log for αε974F1 on Excel (of course Excel, what else are you going to use? Notepad?!?) and want to check all the records logged at 7 AM on any day. You don’t have all the time in universe to filter records one at a time. You don’t want to write a formula or something else as it is too early in the morning and the nearest Starbucks is 7 light years away. So what would you do?

Use filters of course.

Continue »

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

Published on Jun 26, 2017 in Excel Howtos

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

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.

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

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

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

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

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?

Continue »

## How to highlight overdue items [video]

Published on Aug 3, 2015 in Learn Excel

We, adults can’t escape three things:

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 »

### Get FREE Excel & Power-BI Newsletter

One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free.