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.
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

jwgreen1986

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

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
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
 

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,768
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

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
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,445
Office Version
  1. 365
Platform
  1. Windows
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

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

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
Did you check the examples?
You can put each example case and the expected result for each of them.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,790
Messages
5,524,893
Members
409,609
Latest member
Channingz

This Week's Hot Topics

Top