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

Irritating date formula issue

C

Cantonalives

Member
Hi all,
Help please.

Right, I have dates across the top of my sheet like this:

e F
01/01/14 01/02/14
30/01/14 28/02/14
And in columns down the side some think like this:

A B C
Name Started Work Finished Work
Mr J 15/06/2015 (blank)
Mrs Y (blank) 15/03/2015
Mr P (blank) (blank)

Basically I need a formula that will work out if they were active in each month and then for what amount of days. But mainly the first aspect. At the moment the the fact that there are blanks too is really throwing my formula.

Thanks and hope this makes sense,



Best wihes,

-CL.
 
It depends on how you want to treat the blanks.

If Finished Work date is blank, then you can assume the activity is still going on.
If Started Work date is blank, then you can assume the activity started at date 0 (or the minimum of the dates in first rows).

If you go with these assumptions, then you can use formulas to calculate the overlap of dates.

Check out below page to learn how to do this:

http://chandoo.org/wp/2010/07/07/days-overlapped-excel-formula/
http://chandoo.org/wp/2010/06/01/date-overlap-formulas/

All the best.
 
Hi,
Dummy sample attached. Please note that logic nearly works. Sadly does nothing with working days if dates fall within a month. But main issue is that one piece of logic fails as highlighted.


Thanks,

-CL
 

Attachments

  • Book1.xlsx
    10.5 KB · Views: 2
You need to add argument to exclude when Finish has date when Start is "(blank)".

Modify your formula to following.
In D6: =IF(AND($B6="(blank)",MAX(D$4,D$5,$C6)=$C6),D$3,IF(COUNTIF($B6:$C6,"(blank)")=2,D$3,IF($B6>D$5,0,IF($C6<D$4,0,D$3))))

However, I've got a question.

You have fixed workdays for every month. Is this desired?
If not, consider using NETWORKDAYS function to make it more dynamic. I can show how to make it account for start day in middle of the month.
 
Brilliant - thank you. I am deliberately using the 17.5 as the number of working days.

However it would be great to account of a percentage of that if someone finishes in the middle of a month etc?? Brilliant if you could integrate into this formula?


Thank again -


-CL
 
Ok, in that instance, can you explain the logic to arrive at 17.5 days?

If not, I'd suggest something like below.
% workday = (End Date of Month - Start Date)/Days in Month
Workday = %workday x 17.5

Let me know if you are fine with above logic and I'll incorporate it in the formula.
 
That logic looks good thanks... The 17.5 is a number that is deemed to be acceptable for workers. This number then averages out of the year and allows for annual leave.

Thanks,


-CL
 
Sorry - me again.. Actually it works for the start of the month, but not with an end date..

So if someone starts on the 5th it works, and calculates days in the first month, but not for example if they finish on the 5th. Could both be incorporated at all if possible pls?

Thanks,


-CL
 
Ah, thought that end date was always end of month since you had them all at end of month in your example.

I'm bit tied up today at work. Let me see if I have some time this afternoon/evening.
 
See attached.

Added Row 19 & 20 to demonstrate (19 for when both start and end date occurs in same month, and 20 for both being in middle of the month, but on different months).
 

Attachments

  • Book1 (4)_S2.xlsx
    13.6 KB · Views: 9
You must log in or register to reply here.
Back
Top