• 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.

Count days

S

secema

New Member
Please help me (sorry because my English languaje isn´t very good).


How I calculate lost workdays in C2 and D2, from two dates, example: from 01-25-2012 in A2, to 02-14-2012 in B2?. I´m going to copy in next rows with other examples.


How many lost days on january (C2), and how many on february (D2)?


Thanks a lot from Mexico
 
Hi ,


There is no problem with your English ; my doubt is over the words "lost workdays" ; can you say exactly what the definition of lost workdays is ?


1. How many workdays are there in January and February ?


2. Given the date 01-25-2012 , do you want to calculate the lost workdays between 01-01-2012 and 01-25-2012 , or do you want to calculate the lost workdays between 01-25-2012 and 01-31-2012 , or even between 01-01-2012 and 01-24-2012 ( excluding 01-25-2012 ) or between 01-26-2012 and 01-31-2012 ?


Narayan
 
Lost Workdays - Refers to the particular number of days an employee is absent from work due to an injury or illness or the number of days which the employee is on restricted duty.


FILE-Link


https://docs.google.com/spreadsheet/ccc?key=0Aiq5OVySd0aWdEF6OXFsWVNIaGxkS0NXY2dsaDlMclE


Thanks friends
 
Hi ,


Can you try this formula in D2 ?


=IF(AND(MONTH($B2)=MONTH(D$1&0),MONTH($C2)=MONTH(D$1&0)),$C2-$B2+1,IF(MONTH($B2)=MONTH(D$1&0),EOMONTH($B2,0)-$B2+1,IF(MONTH($C2)=MONTH(D$1&0),$C2-($C2-DAY($C2)+1)+1,0)))


Copy it across and down.


Basically , it is implementing the following logic :


1. Either both the start date and the end date are in the month in row 1

2. Only the start date is in the month in row 1

3. Only the end date is in the month in row 1

4. Neither the start date nor the end date is in the month in row 1


The function EOMONTH is available only if the Analysis Toolpak add-in is installed.


Narayan
 
It´s amazing the logic, very good!


but it doesn´t work in D4, maybe I don´t have add-in installed


thanks a lot
 
Hi ,


Sorry , but I did not think that the start date and end date would span more than 2 months. The problem is with the formula itself , not because of an add-in.


Before I revise the formula to take this into account , can you say whether the months in row 1 ( Jan , Feb , Mar ,... ) can be replaced by actual dates e.g. 1/1/2012 , 2/1/2012 , 3/1/2012 ,... ? This will make the formula a little less complicated.


Narayan
 
Hi ,


Can you download the file and check it out ?


https://docs.google.com/open?id=0B0KMpuzr3MTVT1RQa0ZfZGZySHM


Narayan
 
It´s so beautiful, really work


You´re truly ninja


You have finished my headache


I really appreciate your help
 
You must log in or register to reply here.
Back
Top