Date Fields

[jwgreen1986]

right i need some help with this. I have to work out a pro rata for people based on the amount of days they have worked over a quarter period.

I have a start date for them and an end date for them. first off i want to work out how many active days they have worked over the quarter. so from 1/1/19 to 31/03/19 there are 89 days. so i want to create a formula that will look at the start date of a person and tell me how many active days they have been working for the company. similarly if someone has left the company i would like to compare there end date and tell me how many days they have worked over the quarter. i will also need to set something up for the other quarters of the year as well so in the future it can just look up the working days for that quarter and tell me how many days that person has worked going off their start/end date.

after i have done this i then would like to put a formula that will input a simple yes or no as to whether that person has worked during a certain month.

so say for example joe bloggs started working on 13 feb 2019, i would like it to say no in a jan column but then say yes in feb and mar.

 

[jwgreen1986]

anyone know why the below formula is not working. it is working out the days between 01/01/2019 (start date of quarter) and a persons end date. however it isnt working out the days when someones start date is after the beginning of the quarter. this has really got me stumped.

basically i have the start date of a quarter and end date of the quarter in cells l33 and m33.

if the person has a start date before the quarter start date (l33), and they have not left the company then they have worked the full quarter i could even just have it work out the days between the quarter starter date (l33) and quarter end date (m33).

if the persons start date is before the quarter start date (l33) but they have left then work out the amount of days between the quarter start date (l33) and their end date.

finally, if someone has a start date of after the quarter start (l33)then work out the amount of days between their start date and the quarter end date (m33)

the formula i have is =IF(AND(L7<$L$33,M7>$L$33),DATEDIF($L$33,M7,"d"),IF(AND(L7<$L$33,M7>$M$33),"FULL",DATEDIF(L7,$M$33,"d")))

L7 equals employees start date
$L$33 equals quarter start date (01/01/2019)
M7 equals employees end date
$M$33 equals quarter end date (31/03/2019)

this really has me stumped and is hurting my head.

 

[DanteAmor]

Check the following

Excel Workbook
	A	K	L	M	N
1
2
5
6			STAR	END	RESULT
7			20/12/2018	01/04/2019	FULL
8			20/12/2018	05/03/2019	63
9			10/01/2019	04/02/2019	80
10			02/01/2019	01/04/2019	FULL
11			08/01/2019	31/03/2019	FULL
12
33			01/01/2019	31/03/2019
Sheet

 

[Jeffrey Mahoney]

In my opinion you can use SUMIFS and COUNTIFS to get what you need. What format is your data in? Can you use one of the forum tools to copy and paste your data here?

For this to work, you would need a column of Names, a column of dates, a column of hours they worked on each date. Then you need a list of all the names.

Assume Column A is the name, B is the Date, and C is the hours. Assume Row 2 is the headers for the columns. Assume Cell A1 is the name you want to review, B1 is the beginning date, and C1 is the end date :
=COUNTIFS(A3:A1000,A1,B3:B1000,">"&B1-1,B3:B1000,"<"&C1+1,D3:D1000,>0) - count the days between the first and last date somebody worked.

Jeff

 

[DanteAmor]

Change:

Excel Workbook
	A	K	L	M	N
1
2
5
6			STAR	END	RESULT
7			20/12/2018	25/12/2018	OUT
8			20/12/2018	05/03/2019	63
9			10/01/2019	04/02/2019	80
10			02/01/2019	01/04/2019	FULL
11			08/01/2019	31/03/2019	FULL
12
33			01/01/2019	31/03/2019
Hoja2

 

[Joe4]

I have merged your two threads together. In the future, please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will typically be locked or deleted (rule 12 here: Forum Rules).

 

[jwgreen1986]

this one is just showing full for all of them. even if the staff member has a end date before the quarter end date or if someone has a start date after the quarter start

 

[DanteAmor]

Did you check the examples?
You can put each example case and the expected result for each of them.