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 AN154) 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:
where the consultant's initials for
the calculation are in cell AD154 and column I is where a consultant puts their initials in the data section. Column K is the
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
=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
sorry for the length of my post, it's hard to explain concisely!
In fact, I have just
realised I can upload my file. Cell A135 is where I would like to enter the date range by formula.
Cell AN154 is
where I would like to modify my formula to check if the invoice date (ie check the whole of column S) is within the month of
That way I can 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 increase problem? Thanks in advance.