# information provided by dates

#### jwgreen1986

##### Board Regular
ill try to keep this simple. basically i have a start date and an end date of an employee.

the first thing i want to do is work out how many days a person has worked over a quarter. so i need something that will work out how many days there are in jan feb and mar and add these together. then do the same for apr may jun and so on.

next i want to look at a persons start date and work out how many of the days they have worked in a quarter. for example, if they started on 5th feb there pro rate would be 23+31 which equals 54 days out of 89 (jan - mar).
i would also like to do the same but with an end date. so if someone leaves the company id like to know how many days they have worked before they have left.

the next thing i would like to do is populate some month fields. so i would have the 3 months of the quarter listed. then looking at their start date and end date i would like to simply put yes or no in the month cell for each employee as to whether or not they were working for the company in said month. for example if an employee started working in the company on 17th feb they would have no in jan but yes in feb and mar. again i would then like to do the same for leaving date.

this has really got me stumped. im trying to think of formulas i can use but can think of any. is there a simple way of doing it, or will i need to change the start/end date to a different format to work out the days they have worked?

any help would be appreciated

Last edited:

### Excel Facts

To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

#### AlanY

##### Well-known Member
Row 2 - True is month at work
Row 3 - True is Start month
Row 4 - True is End month

Row 5 - Work days in month
Row 6 - Work days in quarter

#### jwgreen1986

##### Board Regular
this is absolutely fantastic and exactly what i was looking for. just one little question with regards to the formula for E5. is there any way that this can be summed so it is in one cell. i like how you have it spread out per month, but im wanting it in one cell (just for the total days worked) so it equals up all the days for the quarter that a person has worked based on their start date

#### jwgreen1986

##### Board Regular

so instead of it working out the days worked month by month, is there a way it will just show the overall days worked. i understand that is what E6 does, but is there a way to get to this without the need of E5, or by only having one formula?

#### jwgreen1986

##### Board Regular

how do i put a table on to explain a little clearer
****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">
ABCDEFGHIJKLMNOP
1StartEndOct-18Nov-18Dec-18Jan-19Feb-19Mar-19Apr-19May-19Jun-19Jul-19Aug-19Sep-19
2Employee 102/12/201820/07/2019In workFALSEFALSETRUETRUETRUETRUETRUETRUETRUETRUEFALSEFALSE
3StartFALSEFALSETRUEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSE
4EndFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSETRUEFALSEFALSE
5work days (month)2313031283130313020
6work days (quarter)23130909120

</tbody>
</body>

#### AlanY

##### Well-known Member
i think i know what you're after, but i'm off now.
i can have a look tomorrow if you still haven't found a solution

#### jwgreen1986

##### Board Regular
so this is what mine looks like roughly. the true/false part works fine on mine and was just what i wanted. with regards to the days worked i want it in one column like this called pro rata. so if they started before the first day of a quarter (01/01/2019) and have not left the company then they have worked the full time. if they have started after the first day of the quarter then work out how many days they have worked up until the end of the quarter (31/03/2019).
if they have left the company then work out how many days they worked between the start of the quarter and their end date. im thinking i am going to need a quarter start date and quarter end date somewhere so i can change these each quarter for future reports. its just the calculation i cant figure out to give me the days they have workedin the quarter. i was hoping to avoid expanding the spreadsheet further because it already has a lot of info on it. hence wanting a formula to just put it in one cell.

ABCDEFGHIJKLMNOP
1StartEndJan-19Feb-19Mar-18Pro Rata9Feb-19Mar-19Apr-19May-19Jun-19Jul-19Aug-19Sep-19
2Employee 102/12/2018In workTRUETRUETRUE??????
TRUETRUETRUETRUETRUETRUEFALSEFALSE
3Employee 203/02/2019StartFALSETRUE
TRUE??????FALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSE
4

EndFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSETRUEFALSEFALSE
5
work days (month)
231

3031283130313020

6
work days (quarter)
231

30

90

91

20

<tbody>
</tbody>

[/QUOTE]

#### AlanY

##### Well-known Member
the work days per quarter are on the right side of the sheet
as its stand an empty column between the 2 sets of data (Col P) is needed

Replies
6
Views
48
Replies
2
Views
47
Replies
3
Views
62
Replies
2
Views
38
Replies
7
Views
63