Finding 12 Months From Today In A Range

Jan 22, 2014

I am trying to update our sickness spreadsheet for work to make it a little more 'intuitive' and less cumbersome to use. There are dates (by day) accross the top, with staff names below. If someone is off sick, the manager writes the number of hours lost to sick in that day, under the correct day, then uses back color / font color to show its sick.

Our work uses a 3 periods of absence or more over a rolling 12 months and the staff are issues with a warning, same with over 8 days off sick. If they are off for longer than 14 days they are long term sick.

I would like excel to show whether each staff member has met any of those criteria in the last 12 months by either highlighting their name or putting text into an adjacent cell.

I have tried using offset, named ranges and anything else I could think of to automatically increase the range +1 day very day, but I cant !

Attached File : plan of staff for 2014.xlsx

View 7 Replies


ADVERTISEMENT

Finding All Dates In A Column That Are In The Range Today To A Week From Now

Sep 21, 2008

Im trying to search a column (A), that has a list of dates (not in order), for the row in which the dates are equal to or greater than today and less than or equal to a week from today. I then want the information contained in the rows with these dates to be transferred to another sheet and ordered by date.

View 13 Replies View Related

How To Get The Date Of 3 Months Ago From Today

Aug 30, 2013

I have to figure out the date of 3 months ago from today. The rule is:

1) if today is the month end (e.g. 30/06/2013), then 3 months ago will also be the month end (e.g. 31/03/2013).

2) if today is not the month end (e.g. 10/06/2013), then 3 months ago will have the same day (e.g. 10/03/2013). But this has some exceptional case, because some month has 30th while other month does not. For example, if today is 30/05/2013, then 3 months ago will be in February which does not have 30th. In this case, just make it as the month end of 3 months ago.

View 5 Replies View Related

SumProduct Using Today Date (rolling 12 Months)

Jul 22, 2014

I have a formula which works =SUMPRODUCT((MONTH(F7:M7=2) *(F12:M12="S"))) but only calcautes the S over certain dates. (F7:M7) Are the dates. i need to incorporate the today formuala so it works from 12 months from today. 12 months rollings (so figures wll change daily).

01 January 2014
02 January 2014
03 January 2014
04 January 2014
05 January 2014
06 January 2014
07 January 2014
08 January 2014
#########
#########

[code]....

View 9 Replies View Related

Excel 2010 :: Add 12 Months To Date And Then Subtract Today

Apr 25, 2014

I'm in Excel 2010, and the cell with the date I want to work from is G22e?

EDIT: I'm trying to get the difference of the (date+12 months)-TODAY() to appear in months and days. (I also meant H22 not G22)

Here's the latest thing I tried (that doesn't work):

=IF(DATEDIF(H22,TODAY(),"y")>=1,DATEDIF(H22,TODAY(),"y")&" yrs, "&DATEDIF(H22,TODAY(),"ym")&" mths, "&DATEDIF(H22,TODAY(),"md")&" days",IF(DATEDIF(H22,TODAY(),"ym")>=1,DATEDIF(H22,TODAY(),"ym")&" mths, "&DATEDIF(H22,TODAY(),"md")&" days",DATEDIF(H22,TODAY(),"md")&" days"))

EDIT #2: I guess I should probably note that the date in H22 is the result of another formula

=EDATE(G22,12)

View 7 Replies View Related

OFFSET With Today's Date To Show Last 6 Months Data

Dec 16, 2005

I have a data table with monthly data in columns (65 rows deep), with the months (in format dd/mm/yyyy but showing as Dec 05) running across Row 4.

I want to be able to use OFFSET to identify the current and previous 5 months, in order to dynamically chart various items in the last 6 months worth of data.

The charting bit I'm okay with, and I realise I need to assign Names for this to work, but I'm struggling with the OFFSET & date combination.

I have the following but it starts from a defined reference cell;

=OFFSET('BO Data'!$L$4,0,('BO Data'!$4:$4)-1,1,-6)

View 9 Replies View Related

Excel 2010 :: Find Months And Days Between Today And Another Date That Is Result Of Function?

Apr 25, 2014

I'm in Excel 2010, and the cell with the date I want to work from is H22.

I'm trying to get the difference of the (date+12 months)-TODAY() to appear in months and days.

Here's the latest thing I tried (that doesn't work):

=IF(DATEDIF(H22,TODAY(),"y")>=1,DATEDIF(H22,TODAY(),"y")&" yrs, "&DATEDIF(H22,TODAY(),"ym")&" mths,
"&DATEDIF(H22,TODAY(),"md")&" days",IF(DATEDIF(H22,TODAY(),"ym")>=1,DATEDIF(H22,TODAY(),"ym")&" mths, "&DATEDIF(H22,TODAY(),"md")&" days",DATEDIF(H22,TODAY(),"md")&" days"))

I should also probably note that the date in H22 is the result of another function.

=EDATE(G22,12)

View 5 Replies View Related

Finding End Of Week Prior To Today's Date

Aug 15, 2007

I am being supplied with a date (but not weekday) in a report. How do I find the date of the prior Saturday without having the weekday supplied?

View 4 Replies View Related

Finding Months Between Two Dates?

Dec 23, 2013

finding a formula to calculate the number of months between two dates. I've attached an Excel spreadsheet with sample data. Some are in the future, and some are in the past.

Excel Help.xlsx

View 9 Replies View Related

Count No Of Unique Months For Finding Average

Mar 11, 2014

I am trying to calculate the average headcount for different categories (over a certain period of time, by product, by job type etc) and I want to do it using a formula instead of a pivot table.

I have this set of data with a detailed headcount for every month. And I can find out the total HC for different categories using sumif/sumifs but if want to find out the average for a certain period, I need to split that total to the number of months where I have a HC.

For example, if the HC per month would be:

May 2013 - 12
June 2013 - 10
July 2013 - none
August - 5

in a 4 months period I would have a total HC of 27 and the average would be 9 (27/3 - July shouldn't be counted). How can I get the average in a single formula?

Month
Type
L/H
Prod type
HC

[Code].....

View 9 Replies View Related

Creating View By Filtering 2 Columns By Date (both Within 3 Months Of Today Date)

Mar 20, 2014

Trying to do a linkback from another post located here but not having much luck doing it: [URL]

I'm working with 2 date columns and trying to filter a view to only include projects with dates within 3 months of today's date.

I've attached a current working file of the data and the end result i'm hoping to achieve via a macro of some sort.

I've manually got it to work via formula by inserting 2 additional columns (highlighted yellow) which determine if the dates "YES" fall in this 3 month time frame of "" blank if not.

create a macro which does all of this automatically without modifying any columns if this is possible

View 5 Replies View Related

Filter Data By Date Within 3 Months Of Today Date?

Mar 20, 2014

I'm trying to filter 2 date columns to include only data containing dates within 3 months of today's date.

I see there is a data filter option for "next quarter" but not 100% sure if this covers the quarter from today's date?

View 3 Replies View Related

Sum All Of The Numbers If Today Falls Within The Range

Jan 12, 2009

If I have A1 ( and the rest of row 1) that equals a standard Date Period such as shown below, What would the formula to be to sum all of the numbers if today falls within the range, as well as sum previous ranges.

For instance, If today were 02/15/09, it would only sum A2
If today were 03/15/09 it would sum B2 as well as previous time periods (B2 + A2)
If today were 4/15/09, it would sum C2 as well as previous time periods (C2 plus B2 and A2)

A1........................................B1......................................C1
"02/05/09 - 03/02/09".............."03/03/09 - 04/01/09"............"04/02/09 - 05/05/09"
15........................................21......................................36

View 9 Replies View Related

Lock Range If Date < Today

Apr 21, 2009

I need to lock cells in protected and shared workbook if cell value in colA is 2 days less than today

Eg. if A5=today()-2 then it should lock range A5:I5

View 9 Replies View Related

Change Sum Range Depending On Today Date

Mar 14, 2012

I have a budget worksheet that uses a spending forecast sheet to determine where the spending level for each budget code should be to the end of the current month. In other words, the Forecasted YTD To Month End fomula for each budget code in the budget worksheet is simply:

=SUM(Forecast!D8:F8)

Where column D is Jan, E is Feb, F is Mar etc in the forecast which distributes the budgeted amounts by month.

In each row on the budget sheet I want to evaluate today's date and if we're in March I want the formula to read as above but when we move to April I want it to use G as the end column in the range to sum.

SUMIF seems cumbersome in this circumstance and I wonder if a UDF might suit better.

View 9 Replies View Related

Counting Dates In A Range :: Today And 7 Days Ago

Jul 6, 2009

I need to do a total of the dates entered in the RAW DATA sheet that fall in between today's date and 7 days ago. I am comparing it to B3 in sheet 2. Have tried a COUNTIF but cannot get it to work.

View 4 Replies View Related

Countif (?) Within A 2 Week Date Range Of Today

Sep 11, 2003

I have a workbook which contains 1 spreadsheet that contains data entry for approximately 20 employees. The workbook then contains a separate sheet for each employee to display the detailed information

Column A stores the dates from Jan1 to Dec 31
Row 1 contains the employees names.
The data entered consists of approximatle 4 different 1-letter codes as to what transaction occurred that particular day.

What I would like to do now is be able to count the number of cells that contain a code for 2 different time periods. I would like for it to count 2 weeks ago and separately count 2 weeks in the future.

In trying to get this last calculation, I've added a column for WEEKNUM next to the date (column B) and used somethign along the lines of
=CountIF(C2:c366,Weeknum(Now()-2)) and also tried +2. Neither have worked.

View 9 Replies View Related

Pull Dates Which Fall Into The Range From Today Until 30 Days From To Day

Apr 23, 2009

I got this problem I canít solve, maybe it is easy to solve, but I am fairly new to writing functions in excel.

I got a lot of different dates in single column, what I need is to pull the dates if they fall in to the range, from today till 30 days from now.
I understand it calls for array formula.

View 7 Replies View Related

Dynamic Range For Months

Sep 27, 2007

I have a list of months (Jan to Dec) in column "A" sorted in ascending order. The current month is Sept. I have no idea which row the range will start or end on (Since the # of records vary from month to month) and there is data both above and below the range. I need to create a dynamic range of the current month. The dynamic range must start and end with the current month (eg Sep).

View 9 Replies View Related

Split Value By Months Within Date Range?

Mar 21, 2013

I'm trying to split an amount within a given month:

Customer
From
To
Total number booked
Total amount booked

A
3 March 2013
5 May 2013
458
£5,000

Therefore, assuming that the number and amount booked is done evenly on a daily basis and considering the fact that in March, the period is 3 March - 31 March, while in May it is 1 May - 5 May, is it possible to have a formula that calculates the amount by month?

I have been using =D2/(DAYS360(B2,C2)) to find out the daily number booked, then multiplying this by the days in a given month to find out the monthly value, but is there a more elegant way of doing this where I can define the month in another sheet (Col. B), to return the following in Col. C and D?

Customer
Month
Monthly number booked
Monthly amount booked

A
March 2013
207
£2,258.06

A
April 2013
214
£2,338.71

A
May 2013
30
£322.58

View 2 Replies View Related

Dynamic Range For Last X Months In Column/Row

Apr 15, 2008

I need to create a validated list of month choices, and the list needs to be filled with the names (Month and year, I.E. Feb-07) of the last 13 months for which we have data.

(We get our data about 8 weeks late, so being that it is mid april now I only have data now up to the end of Feb-08. I have created a chart in which the viewer can change the month to view but up to now it has only been for the current year, now they want to see the past 13 months, obviously I don't want to go in and change this every time the next months' data is entered.

So, what offset formula do I use to reference the month-year names which are spread across Row 1, presently in columns C thru N, and the next months data (March) will go in column O, then April's in column, P, etc

I have tried several variations of this; =OFFSET(C1,0,0,0, COUNTA($1:$!)) but how do I get only the last 13 months?

View 3 Replies View Related

Determine If 2 Dates Are Within Range Of Months

Jun 19, 2008

I have found the answer to several of my excel questions here in the past, but I'm completely stumped on this one.

I have two dates:
Jan-08
Sep-08

And I need to know if the range of these dates (January-September) falls between the range of two other dates:
Jan-08
Jun-08

Basically I'm checking to see if the range of months Entered falls in the First half of a year or the second, or both

View 8 Replies View Related

Conditional Format - Dates Greater Than 18 Months And 24 Months?

Dec 6, 2013

I have a date column (Column E: Date Entered) on my spreadsheet that I need to set conditional formatting on. There are two conditions:

1) 18 months from the date in the cell needs to be highlighted yellow

2) 24 months from the date in the cell needs to be highlighted red

View 6 Replies View Related

Named Range References Prior 12 Months

Mar 28, 2014

My goal is that the formula in this named range references the prior 12 months. This does reference a twelve month range in my workbook, but not the prior 12 months based on this month being MARCH.

DATES =INDIRECT("sheet1!A"&MONTH(TODAY())+1&":A"&MONTH(TODAY())+12)

View 1 Replies View Related

Group Range Of Cells According To Months Written In One Column

May 15, 2014

Sample workbook for Excel forum.xlsx

I have multiple lists like company A company B so on and on. each has multiple accounts maturing at different dates.how can I group accounts maturing in one particular month? the maturing dates are different in some accounts and same in others.

company A
account no maturity date
account no maturity date
company B
account no maturity date
account no maturity date
so company C, company D etc..

All are on different sheets, I tried to figure out with Vlook up, and filter, but could not make formula.

View 7 Replies View Related

Number Of Months In Year 2007 From Date Range

Jan 12, 2008

I have a "start date" and an "end date". Is there any way to tell how many months are included in 2007 from those two dates using a formula? For instance, from 01-Feb-04 to 01-Feb-07, there is 1 month in 2007 (January). And likewise, 01-Jan-05 to 01-Jan-08 there is 12 months in 2007.

As noted, I have the start and end dates. I don't have the "how many months in 2007".

Start Date End Date How many months in 2007
01-Feb-0401-Feb-07 1
01-Jan-0301-Jan-06 0
01-Apr-0401-Apr-07 3
01-Feb-0301-Feb-06 0
01-Mar-0401-Mar-07 2
01-Feb-0401-Feb-07 1
01-Dec-0401-Dec-07 11
01-Jan-05 01-Jan-08 12

View 9 Replies View Related

Highlight A Date Range If Today Date Falls Within That Range?

May 1, 2014

I have a employee travel spreadsheet with air travel dates in two columns. I want to be able to create a rule that will highlight all the cells in a row if the date range on that row fall includes today's date. if someone is traveling in the event there is an emergency and I need to know if they are traveling that particular day. I have attached a sample spreadsheet.

View 3 Replies View Related

Elapsed Months (many Months Have Gone By Since Todays Date)

Jul 19, 2009

Cell A1 is a past date. In cell B1 I would like how many months have gone by since todays date. eg. Cell A1 = July 07, B1 would = 24 months.

View 3 Replies View Related

Add Months & Return Decimal As Years & Months

Jul 31, 2007

I am looking for a formula that will add months and return the year. E.g. if I add 1.05 and 1.07 I should get 3.01. i.e. 3 years and 1 month.

View 9 Replies View Related

Copy Range Of Series By Finding / Ref Of Range?

Aug 5, 2014

I have attached xl sheet for ref.

I have value in E2(5),F2(10)=G2(5[sub of F2-E2])

I need to find E2(5) value in B2:B21 and the same of F2 also

After finding that

(eg): E2 value 5 present in B6 cell and it should come to c6 cell same of F2 also

Now It should select C6 to C11 cells.(ref of B6 to B11,ie,5 to 10)

I tried in vba and formuals but i couldnt find solution.

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved