Number of Working days between 2 dates

**Madball** · 10-10-2007, 09:46 AM

Hi All,

I'm currently working on a file that has employee holidays/sickness etc. What I want to do is be able to see is a DATE FROM and DATE TO and next to it the total amount of working days between and including those dates. On top of the since employee's sometimes like to take half days I need it so that it can determine half days as well.

So for example I have an employee going to be off work from 29/10/2007 until 09/11/2007 12pm which is 10.5 days in total, but that includes weekends when I don't want it to. Also it comes out as 11 days at the moment because I can't figure out how to get it to determine is it is half a day.

What formula should I use or if someone could create an example that would be really usefull :D

kind regards

**oldchippy** · 10-10-2007, 09:51 AM

Take a look here

http://support.microsoft.com/kb/259200

http://www.bettersolutions.com/excel...A111880331.htm

**Madball** · 10-10-2007, 10:02 AM

errr that looks like Blah Blah Blah to me lol.....

**Madball** · 10-10-2007, 10:38 AM

OK I have got it to work so that it removes all the bank holidays etc using the NETWORKDAYS() formula, but I have no idea to to get to take into count half days from 2 dates.

**daddylonglegs** · 10-10-2007, 10:57 AM

All dates show 12:00 AM by default do you mean 12:00PM? Assuming that you only have ½ days and not ¼ days or some other fraction....and assuming it could be a ½ day at the start or end of the period try

=networkdays(A2,B2)-MOD(A2,1)-MOD(B2,1)

**oldchippy** · 10-11-2007, 04:14 AM

Originally Posted by Madball

errr that looks like Blah Blah Blah to me lol.....

Actually, it's surprising once you understand a bit more about "Blah Blah Blah", it's amazing what you can get out of it - keep at it!

**Madball** · 10-11-2007, 10:07 AM

OK have have got NETWORKDAYS() to work, so for example there are 10 working days between 29/10/2007 and 09/11/2007. But the problem I'm having is, I cannot get it to figure in half days.

For Example I have in cell A1 the heading DATE FROM and in cell B1 DATE TO. Then in Cell A2 and B2 I have the dates 29/10/2007. In Cell C2 using this formula =NETWORKDAYS(A2,B2) it will display the number 1. This is correct but but want it to display 0.5 if I put in cell A2 29/10/2007 - 12:00pm and in cell B2 29/10/2007.

How do I get this to work?

**daddylonglegs** · 10-11-2007, 10:51 AM

Try my suggestion above

**oldchippy** · 10-11-2007, 10:57 AM

Try this one

**Madball** · 10-18-2007, 11:41 AM

Thanks a lot for this, it works great, Just wondering though, instead of having to put in a date for the Half day is there a way so you can put either PM or AM in and it will do the same?

**oldchippy** · 10-19-2007, 04:01 AM

Here's an alternative with AM or PM count

**Madball** · 10-19-2007, 11:40 AM

OMG lol thanks a lot, I know I'm going to sound like a pain now, but is there a way for it to not count sickness and authorised absence. So where you put "AM and PM" if I put in SICK or ABSENT it wouldn't count those days off?