Spiritseeker
New Member
- Joined
- Feb 20, 2009
- Messages
- 38
Hi all,
I am hoping for help with two date formula queries I have.
I have built a spreadsheet which consultants at my work (a recruitment agency) list their successful job placements on. We record the placements monthly, and each month I simply copy and paste the previous month's 'template' (a 'block' of 60 rows) down into the area immediately below. The 'template' then has all the right formulae in the right place, one of which is a formula to auto increase the month by one. So if the previous month was March 2009, the next pasted 'block/template' will automatically be entitled April 2009. The placements are then summed up automatically per consultant per month with further formulae in the template that make calculations based on the data in the month's 'block'.
Some recent changes to the info we want to see per consultant per month leads me into my 1st question:
Instead of just listing March 2009, I now need (I think, unless Excel can be a bit clever) the date to be essentially the range of the whole month, ie 01/03/2009-31/03/2009 (I am in the UK), or for months with fewer days to cover the relevant period ie 01/02/2009-28/02/2009. Is there a way to do this? And let me explain why I think I need it in my 2nd question (the resolution to question 2 may mean my question 1 is not the best way of going about things).
I have a formula that totals the placement fees per consultant by using a sumproduct. The formula (in cell AM154) basically adds up a fee if the consultant's initials by the fee match the consultant's initials where I want the calculation to take place (cell A135). I now need to have a further if criteria, and the formula is getting a bit complicated, so I would appreciate some advice. The formula is as follows:
=SUMPRODUCT((I136:I189=AM154)*(K136:K189))
where the consultant's initials for the calculation are in cell AM154 and column I is where a consultant puts their initials in the data section. Column K is the placement fee.
I now need to modify the formula so that it checks the date an invoice will be raised for a placement for a certain month (ie I need to know the total value of invoices that will be raised in February for a specified consultant - placements are listed when they are made but we only invoice on the start date of a candidate and due to the nature of jobs a placement could be confirmed one month but the candidate may not start till the next month or even the month after).
So this is why I asked the first question. My modified calculation formula needs to check if the placement will be invoiced in the month range of the 'block' which will be copied & pasted down for the next month and so on. So it needs to sum any fee which matches the relevant initials, if the invoice date is with the date range of the month block that this formula is in. (eg if we are looking at the block for March 2009, for placements made by the consultant with initials MG, the calculaton needs to check the entire spreadsheet for fees associated with MG that will be invoiced in March 2009).
Column S is where consultants list the date for their placement to be invoiced. Column I is where they list their initials. Column K is where they list the fee value. My modified formula will have to look at the total columns rather than just the 60 rows in each 'block' because, as mentioned earlier, placements made one month can be invoiced in later months. Can someone help me with the modification of the formula? And also how to create the self calculating month formula which heads up each 'block' (I currently use
=DATE(YEAR(A67), MONTH(A67)+1, DAY(A67)) where A67 is the previous month in the previous 'block' in the format 01/03/2009.
If it can't be done in one cell, I am happy to use my existing formula on two separate cells as long as the calculation formula can check a date range between a date in one cell and a date in another cell - and as long as the different number of days in each month can be handled by excel somehow? Ie having 01/01/2009 in cell A1 and 31/01/2009 in cell B1, using my above formula would produce 01/02/2009 in cell A2 but 03/03/2009 in B2 - I need it to be 28/02/2009.
sorry for the length of my post, it's hard to explain concisely!
I want to be able to simply copy the block for a new month and the whole spreadsheet is self calculating, apart from cell A67 which is where I need to enter the starting point for the dates manually.
Can anyone tell me what formula I need to put in AN154 and how to do the auto month range increase problem? Thanks in advance. <!-- / message --><!-- attachments -->
I am hoping for help with two date formula queries I have.
I have built a spreadsheet which consultants at my work (a recruitment agency) list their successful job placements on. We record the placements monthly, and each month I simply copy and paste the previous month's 'template' (a 'block' of 60 rows) down into the area immediately below. The 'template' then has all the right formulae in the right place, one of which is a formula to auto increase the month by one. So if the previous month was March 2009, the next pasted 'block/template' will automatically be entitled April 2009. The placements are then summed up automatically per consultant per month with further formulae in the template that make calculations based on the data in the month's 'block'.
Some recent changes to the info we want to see per consultant per month leads me into my 1st question:
Instead of just listing March 2009, I now need (I think, unless Excel can be a bit clever) the date to be essentially the range of the whole month, ie 01/03/2009-31/03/2009 (I am in the UK), or for months with fewer days to cover the relevant period ie 01/02/2009-28/02/2009. Is there a way to do this? And let me explain why I think I need it in my 2nd question (the resolution to question 2 may mean my question 1 is not the best way of going about things).
I have a formula that totals the placement fees per consultant by using a sumproduct. The formula (in cell AM154) basically adds up a fee if the consultant's initials by the fee match the consultant's initials where I want the calculation to take place (cell A135). I now need to have a further if criteria, and the formula is getting a bit complicated, so I would appreciate some advice. The formula is as follows:
=SUMPRODUCT((I136:I189=AM154)*(K136:K189))
where the consultant's initials for the calculation are in cell AM154 and column I is where a consultant puts their initials in the data section. Column K is the placement fee.
I now need to modify the formula so that it checks the date an invoice will be raised for a placement for a certain month (ie I need to know the total value of invoices that will be raised in February for a specified consultant - placements are listed when they are made but we only invoice on the start date of a candidate and due to the nature of jobs a placement could be confirmed one month but the candidate may not start till the next month or even the month after).
So this is why I asked the first question. My modified calculation formula needs to check if the placement will be invoiced in the month range of the 'block' which will be copied & pasted down for the next month and so on. So it needs to sum any fee which matches the relevant initials, if the invoice date is with the date range of the month block that this formula is in. (eg if we are looking at the block for March 2009, for placements made by the consultant with initials MG, the calculaton needs to check the entire spreadsheet for fees associated with MG that will be invoiced in March 2009).
Column S is where consultants list the date for their placement to be invoiced. Column I is where they list their initials. Column K is where they list the fee value. My modified formula will have to look at the total columns rather than just the 60 rows in each 'block' because, as mentioned earlier, placements made one month can be invoiced in later months. Can someone help me with the modification of the formula? And also how to create the self calculating month formula which heads up each 'block' (I currently use
=DATE(YEAR(A67), MONTH(A67)+1, DAY(A67)) where A67 is the previous month in the previous 'block' in the format 01/03/2009.
If it can't be done in one cell, I am happy to use my existing formula on two separate cells as long as the calculation formula can check a date range between a date in one cell and a date in another cell - and as long as the different number of days in each month can be handled by excel somehow? Ie having 01/01/2009 in cell A1 and 31/01/2009 in cell B1, using my above formula would produce 01/02/2009 in cell A2 but 03/03/2009 in B2 - I need it to be 28/02/2009.
sorry for the length of my post, it's hard to explain concisely!
I want to be able to simply copy the block for a new month and the whole spreadsheet is self calculating, apart from cell A67 which is where I need to enter the starting point for the dates manually.
Can anyone tell me what formula I need to put in AN154 and how to do the auto month range increase problem? Thanks in advance. <!-- / message --><!-- attachments -->
