• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Calculate working days

T

TonyNZ

Member
Hi

I have a rather weird question.

NETWORKDAYS formula can work for calculating difference of working days provided in e.g. Cell A1 is the start date and in Cell A2 is the end date.

What if I write the following in Cell A1 (pertaining to Year 2014): "Monday 6 to Friday 10 January", or "Tuesday 14 to Wednesday 22 January". Is there any formula or method to calculate number of working days (with same result as with NETWORDAYS formula)

Thanks in advance
 
unfortunately, the data that I have received is in this format; it should'nt be like this but if there is no answer then I guess I will have to change the entire date and break dates in 2 columns to make it work
 
Hello TonyNZ,
Welcome to the forum!

Can you provide some examples of your data? Specifically, I would like to know how the data is setup if the date range spans months (such as Jan 17 to Feb 23). Remember to include all possible variations of the format of your data.

Cheers,
Sajan.
 
Hi Sajan
You have hit the right note. Data is exactly in the format that you have mentioned i.e. Jan 17 to Feb 23 but entered in Cell A1
Thanks
bs
 
Hi bs,
In your first post, you have the day of week (e.g. Monday) included, but in your last post you are indicating that just the date (e.g. Jan 17) is what is in the format. Which is it? Both?
 
Without some samples, I am not able to help you. Feel free to upload a workbook with your sample data. (You can do so using the "Upload a File" button.)
 
Hi,
For the sample data you posted, you can try the following formula: (put in cell B1 and copy down)
=NETWORKDAYS(TRIM(RIGHT(A1,3)&" " & MID(A1,4,3))+0, TRIM(RIGHT(A1,3)&" " &LEFT(RIGHT(A1,6),3))+0)

Cheers,
Sajan.
 
Thanks Sajan
Following was the result, with Cells in column B formatted as Number. 2610 and subsequent figures tells me what I couldnt understand.
Mon 6 to Thu 16 Jan 2610
Wed 22 to Fri 24 Jan 521
Mon 10 to Fri 14 Feb 1045
Mon 17 to Fri 21 Feb

1044
 
Hi,
Your results seem to be different than mine:
Chandoo-Tonyz.JPG

See attached doc.

Cheers,
Sajan.
 

Attachments

  • Chandoo-TonyZ-Calculating_Working_Days(1).xlsx
    9.1 KB · Views: 4
hmmm very strange lol
I will use the one sent back by you and try to apply on the real data tomorrow. I am sure this will work.
I will come back to you in a day or two.
Many thanks Sajan, much appreciated
cheers
 
Hi Tony,

If you are still facing the unexpected output, You can change a little in sajan's formula.

=NETWORKDAYS(TRIM(MID(A1,4,3)&RIGHT(A1,3))+0, TRIM(LEFT(RIGHT(A1,6),3)&RIGHT(A1,3))+0)

@Sajan..
I think, due to regional setting, Jan 6 is
  • 1-Jan-2006 in dd-mm-yyyy case,
  • 6-Jan-2013 in mm-dd-yyyy case
But, I think 6 Jan is 6-Jan-ThisYear, in both regional setting..
Can you please confirm from your side.. :)
 
Hi Debraj,
I am not sure if regional settings play a role in this case. I am able to enter a date as "Jan 6" or "6 Jan", even though my regional date setting is month-day-year. And yes, when a date is entered, the year defaults to current year.

Are you also seeing a different output when using the workbook I uploaded?

-Sajan.
 
Hi, TonyNZ, Sajan!

First of all, give a look at the uploaded file. In this case there are many factors that are playing jokes:
a) TonyNZ sample data and Sajan Sample data are different: 1st entry, 16 Jan vs. 9 Jan
b) TonyNZ wrote "pertaining to Year 2014" and Sajan's formula just builds Month and Day, ergo assuming 2013
c) I got those weirds numbers (2610/784, 521, 1045 1044) as TonyNZ did, instead of the nice ones (8/3, 3, 4, 4) that Sajan did... because while Sajan wrote the formula for his regional config settings (mm/dd) TonyNZ read it in his (as mine) (dd/mm) -as Debraj(ex-Roy) correctly stated, which I only now happen to read-

In my sample file, column B is the translation to my local language (otherwise how to test it?), and columns C:E has the values/formulas for actual year 2013 and F:H for required year 2014.
Columns C&F are manually set, while D&G have values for mm/dd and E:H for dd/mm. However they all point to column B (Spanish version) so please fix them to column A (English version).

Hope it helps.

Regards!
 

Attachments

  • Calculate working days - Calculating Working Days (for TonyNZ at chandoo.org).xlsx
    10.2 KB · Views: 6
Hi SirJB7, Debraj,
Good review of how regional settings can affect calculations! Something for folks to consider when attempting to apply a formula from one Excel version into another.

-Sajan.
 
Hi, Sajan!
Those who only use a local Excel version may not be aware or these slight formula disasters since they happen only when sending files to people with other environments, and it's logical: they would have never had the chance to test it in other either regional configuration or Excel versions. I.e, I couldn't check the results of my column G (your formulas with 2014) since I get #¡VALUE! errors.
Regards!
 
Hi SirJB7,
Indeed... Unless someone points out that they are using a different version of Excel, I typically assume that US English version is being used. It was interesting to see a "Georgian" version of an Excel file the other day! Certainly not something that I encounter in the normal course of my work!

-Sajan.
 
You must log in or register to reply here.
Back
Top