All articles with 'networkdays()' Tag

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 »

Designing a dashboard to track Employee vacations [case study]

Published on Jan 24, 2013 in Charts and Graphs, Learn Excel

Employee Vacation Dashboard & Tracker using Excel

HR managers & department heads always ask, “So what is the vacation pattern of our employees? What is our average absent rate?”

Today lets tackle that question and learn how to create a dashboard to monitor employee vacations.

What do HR Managers need? (end user needs)

There are 2 aspects tracking vacations.

1. Data entry for vacations taken by employees
2. Status dashboard to summarize vacation data

Continue »

Top 10 Formulas for Aspiring Analysts

Published on Jan 16, 2013 in Learn Excel
Top 10 Formulas for Aspiring Analysts

Few weeks ago, someone asked me “What are the top 10 formulas?” That got me thinking.

While each of us have our own list of favorite, most frequently used formulas, there is no standard list of top 10 formulas for everyone. So, today let me attempt that.

If you want to become a data or business analyst then you must develop good understanding of Excel formulas & become fluent in them.

A good analyst should be familiar with below 10 formulas to begin with.

Continue »

Formula Forensic 020. Bhavik’s Monthly Workingdays Formula

Published on May 3, 2012 in Formula Forensics, Huis, Posts by Hui
Formula Forensic 020. Bhavik’s Monthly Workingdays Formula

Bhavik’s has presented us with a neat formula to determine how many working days are in a month.
This functionality has a wide range of uses in accounting, payroll, staff scheduling, accommodation planning & reporting and general scheduling activities.

Today in Formula Forensics we pull Bhavik’s Formula apart and see what is going on inside and the offer some extensions to increase the functionality of his formula.

Continue »

My trip to Maldives…, [travelogue + bonus Excel tip]

Published on Feb 4, 2011 in Learn Excel, personal, travels
My trip to Maldives…, [travelogue + bonus Excel tip]

Sometime in November, I got an interesting E-mail from a gentleman named Guru. The title said “Excel Workshop in Maldives”. In the email, Guru introduced himself and asked me if I can come to Maldives to conduct few Excel workshops for companies and individuals.

I usually neglect such mails as many times the actual training (or big consulting assignment etc.) will not happen. So I replied to him giving my number and asked him to call me. I was surprised to receive his call. After talking awhile, it was clear that Guru is tenacious and would not take No for an answer.

So we set things in motion and thanks to Guru’s perseverance, I ended up boarding a flight to Male on 22nd of January. This is a story of what happened next…,

Continue »

Holiday Request Form in Excel [Awesome Ways our Readers are using Excel]

Published on Feb 1, 2011 in excel apps, Learn Excel
Holiday Request Form in Excel [Awesome Ways our Readers are using Excel]

Theodor, one of our readers, first emailed me in December asking a question. But he also made a promise to share some of his techniques with us thru Guest posts. Naturally, I was too happy and invited him to share a file or two so that I can use them for articles here.

Later during Christmas holidays, he sent me this beautiful Holiday Request Form made using Excel.

Continue »

How to Calculate Working Hours Between 2 Dates [Solution]

Published on Sep 10, 2010 in Learn Excel
How to Calculate Working Hours Between 2 Dates [Solution]

This post builds on earlier discussion, How many hours did Johnny work? I recommend you to read that post too. Lets say you have 2 dates (with time) in cells A1 and A2 indicating starting and ending timestamps of an activity. And you want to calculate how many workings hours the task took. Further, lets […]

Continue »

How many hours did Johnny work? [Excel Homework]

Published on Aug 27, 2010 in Excel Howtos, Learn Excel
How many hours did Johnny work? [Excel Homework]

Today we have home work for you. You need to help Johnny figure out how many hours he worked on splitting atoms. He has crazy boss who is behind him for that timesheet. Now Johnny needs your help to write the correct formula so that he can tell his boss how many hours he worked and go home.

Go ahead and read rest of this post and tell your answers using comments. Go!

Continue »

Calculate Elapsed Time in Excel [Quick Tips]

Published on Sep 22, 2009 in Excel Howtos, Learn Excel
Calculate Elapsed Time in Excel [Quick Tips]

Calculating elapsed time is very common whether you are managing a project or raising a baby. Elapsed time is nothing but interval between a starting point and the current point in time. We can use excel formulas to calculate elapsed time very easily. In this post, learn how to calculate elapsed time in days, working days, hours, weeks, months, years, minutes and seconds.

Continue »

Networkingdays() an improved version of networkdays formula

Published on Jun 9, 2009 in Excel Howtos, Learn Excel, VBA Macros
Networkingdays() an improved version of networkdays formula

We all know that networkdays() an extremely powerful and simple excel formula can help you calculate no. of working days between 2 given dates.

But there is one problem with it. It assumes 5 day workweek starting with Monday to Friday. Not all countries have workweek from Monday to Friday.

This got me thinking and I ended up writing a user defined formula (UDF) to calculate working days between 2 given dates with any criteria. This will be good for calculating payrolls for temporary workers, offshore partners and of course people working countries where Saturday or Sunday or not usually holidays.

Continue »