Date Fields

jwgreen1986

Board Regular
Joined
Mar 8, 2019
Messages
64
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.
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
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.
 
Upvote 0
Check the following

Excel Workbook
AKLMN
1
2
5
6STARENDRESULT
720/12/201801/04/2019FULL
820/12/201805/03/201963
910/01/201904/02/201980
1002/01/201901/04/2019FULL
1108/01/201931/03/2019FULL
12
3301/01/201931/03/2019
Sheet
 
Upvote 0
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
 
Upvote 0
Change:

Excel Workbook
AKLMN
1
2
5
6STARENDRESULT
720/12/201825/12/2018OUT
820/12/201805/03/201963
910/01/201904/02/201980
1002/01/201901/04/2019FULL
1108/01/201931/03/2019FULL
12
3301/01/201931/03/2019
Hoja2
 
Upvote 0
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).
 
Upvote 0
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
 
Upvote 0
Did you check the examples?
You can put each example case and the expected result for each of them.
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top