# Date Reference In Formula

Jan 4, 2010
I am trying to create a formula, comparing the date in column A to 3 fixed dates, then calculate the value in column C:

=IF(OR(A142=Sheet1!B2,A142=Sheet1!B3,A142=Sheet1!B4),C141-B142+8,C141-B142)

Where Sheet1!B2, B3 and B4 are the fixed dates (5/15/2010, 8/31/2010 and 10/31/2010). I would like to copy the formula into multiple cells, but keep the references to the fixed dates the same. When I do a copy, paste, the resulting formula becomes:

=IF(OR(A143=Sheet1!B3,A143=Sheet1!B4,A143=Sheet1!B5),C142-B143+8,C142-B143)

I need the Sheet1 references to remain the same. Or, can I hard-code dates in a formula?

View 2 Replies
ADVERTISEMENT
Jul 22, 2011

I have a pretty elaborate spreadsheet for my bills. One cell adds up all the bills due during a specific pay period using named ranges. The formula looks like this:

=SUMIFS(NextPmtAmt,NextPaymentDueDate,">=9/9/2011",NextPaymentDueDate,"<=9/22/11")

It works, but when I have to populate cells for upcoming pay periods, I have to go in an edit the dates manually. I'm lazy, and want to simply reference a cell with the date in it instead of typing the whole date out in the formula. What I'd like to do is this:

A2 contains 9/9/2011

B2 contains 9/22/2011

=SUMIFS(NextPmtAmt,NextPaymentDueDate,">=A2",NextPaymentDueDate,"<=B2")

but it doesn't work.

View 6 Replies
View Related
Jan 16, 2008

Using cell contents in formula to Populating cells with values from closed workbooks

I am trying to create a summary table of data (e.g. orders) from previous weeks. In the table I am creating, the left hand column contains the Monday date of the week to be summarised along the row. I want the cells to show the values from other spreadsheets when those spreadsheets are closed, but I want the formula to pick up the date of the week that is in column A. I have attached a sample spreadsheet to try and make it clearer.

View 2 Replies
View Related
Mar 7, 2012

I have this formula below that counts all matching cells that fall between two dates (a Monday to the next Monday) where S2 and T2 are the dates (one week apart).

I have 52 colums ie one for each week.

=SUMPRODUCT(($G$3:$G$1000>=S2)*($G$3:$G$1000

View 7 Replies
View Related
Feb 20, 2008

i want to return a value from a cell in another workbook where part of the the workbook name is determined by a date set on the current open sheet.

so on my open sheet i have a cell where the user can change the date and i want the below formula to then use the date to look at the relevant file.

any ideas would be appreciated. know how to do this in VBA but no clue with formulas.

what i am trying to do is concatenate part of a file name and a cell value which contans a date

=VLOOKUP(A4,[accountsummary17022008.xls]Sheet1!$A:$B,2,FALSE)

View 3 Replies
View Related
Aug 14, 2006

I have the following formula in cell L51 of all sheets calculating the volume depending on the monthly index that is chosen from the drop down menu in a particular sheet. =If(MIndex=0, SUM(D33:L50),If(MIndex=1,SUM(D34:L50),If(MIndex=2,SUM(D35:L50), 0))). I am getting the following message and I do not understand what it is about.

Microsoft Office Excel cannot calculate a formula. Cell references in the formula refer to the formula's result, creating a circular reference. Try one of the following

View 3 Replies
View Related
Oct 11, 2008

I need to create formulas that reference a single date and output date ranges. The objective is to have a person input a Monday date in any given month and receive a four weeks out worth of dates and ranges. For example: In a lone cell, the person inputs 10/13/08. Automatically, the sheet produces the next full week range: October 19 – October 25 in a single cell and also produces a cell for each date. Example: Sunday 19, Monday 20, Tuesday 21, etc…. It should look like:

Monday Date:

10/13/08

October 19 – October 25

Sunday 19

Monday 20

Tuesday 21

Wednesday 22

Thursday 23

Friday 24

Saturday 25

and then repeat for three more weeks. I thought I had it figured out until the month changed. The dates continued in October instead of adding a month. This report will be ran weekly, so simply adding a +1MONTH to some cells will not benefit me as I’ll have to change the formula every week. I want the formula to compute the data without any manipulation over the next several years. The only change will be the Monday date.

View 2 Replies
View Related
Mar 6, 2008

A 'Days Attended' cell (N8) and a 'Days Absent' cell (O8). N8 needs to count the number of "Present" values there are on another worksheet. The other worksheet has dates across the top and names down the side.

When i use

=COUNTIF("Attendance!C9:Z9", "Present"),

and the next date comes along the formula changes to

=COUNTIF("Attendance!D9:AA9", "Present")

ie. the reference moves a column across - the new date's absent or present is not counted. Using =COUNTIF(INDIRECT("Attendance!C9:Z9"), "Present"). is no good because when i add a new name i need the row reference to move down as a row is inserted. ie. both person's formulas count the same row. So, my question: I need the columns to stay the same - C:Z (leyway for future dates) and the rows to change as i insert or delete people from the system.

View 2 Replies
View Related
Feb 15, 2010

I have lets say 12 months of data. I have formulas that reference the latest 6 months. When I insert a new column to input a new month, how can I make the formulas include the new months without manually updating them.

EXAMPLE:

12 months of data exist in cells B3:M3 going from B3(oldest) to M3(newest). Formulas reference latest 6 months of data in cells H3:M3. When a new month hits, I insert a column after column M.I would like the formulas to now reference cells I3:N3 which is now the newest 6 months.

View 14 Replies
View Related
Jun 2, 2006

I need an IF formula, that states if a date is more than a month after the date in the cell then it is timely and if it isn't then it is not timely. For example the date in A1 is 12/11/05, if the cell is A2 is 13/12/05 (or any date after that) then A3 should read Not Timely, if A2 is 12/12/05 (or any date before that, including a date before the date in A1) then A3 should read Timely. Is there any way to do this?? At the moment I am having to go through manually and put in either Timely or Not Timely.

View 2 Replies
View Related
Mar 26, 2009

=INDEX(INDIRECT('Quote Detail IP'!$C$10&'Quote Detail IP'!$C$5&"!$A:$DC"),MATCH(B1,INDEX(INDIRECT('Quote Detail IP'!$C$10&'Quote Detail IP'!$C$5&"!$A:$DC"),,1),0),MATCH(A1,INDEX(INDIRECT('Quote Detail IP'!$C$10&'Quote Detail IP'!$C$5&"!$A:$DC"),1,),0))

Where

A1= "M16" and B2= "185%RPIT630"

'Quote Detail IP'!$C$10&'Quote Detail IP'!$C$5=QxTermAge63

Can some on tell me why this is raising a Circular Reference!!

View 9 Replies
View Related
Jan 8, 2012

How do I change a formula cell reference based on another cell's reference? I'm building a schedule that looks to a task's trigger and adds days based on that relationship. All entries in column "A" will be text and all cells in "B" will be the simple formula "=A2" or "=A3". Due date is calculated by adding the value in "C" to the preceding date in column "D". In the spreadsheet below, the trigger for "Budget set" is "Specs written" with 3 days added to the previous due date.

________A________________B_____________C_________D

1 Task___________Trigger_____________Days_____Due Date

2 Design begins__Proj OK______________10____10-Jan

3 Specs written__Design begins (A2)____5____15-Jan (D2+C3)

4 Budget set_____Specs written (A3)____3____18-Jan (D3+C4)

If the trigger for A4, "Budget set", changed from A3 to A2, is there a way that the formula that determines the due date in D4 could read the trigger cell reference in B4 so that the value in the corresponding row in column "C" is added in the date column?

View 4 Replies
View Related
Jan 18, 2010

How can I reflect the correct trainee count for Day1-Batch1?

Currently columns F,G,H computes for expected, actual and total variances of trainee respectively reference to the training date J3.

Since this is a 2-day per batch even, I could not capture the 1st day trainee total of every batch because I divide the sum by 2 (2 day / training batch). The sum is only corrected on the 2nd day.

Attached herein is the sample file.

View 9 Replies
View Related
Apr 6, 2012

Basically I have a row of dates

so,

B1 - 01/04/2012

B2 - 02/04/2012

etc...

And I have tabs for each of those dates aswell.

Basically I want to pull some figures out of the tabs but use the dates in the reference.

So i need a formula that will look at the date in cell B1, use the date to find the tab and then say find the value of cell G12.

View 4 Replies
View Related
May 22, 2006

Trying to use a the getpivotdata fomula with date as an argument, refer to with a cell. e.g. =GETPIVOTDATA( 'May Table'!A1, "SICK " & S20). where S20 is a date format

View 2 Replies
View Related
Jul 21, 2014

I'm having serious difficulties with getting together a formula for a little bit complicated lookup.

I have 2 tables. In the first table I have categories in the first column (e.g. red, blue, green), in the second column I have dates (DD.MM.YYYY). And in the third column I would like to get the values from the second table.

In the second table I also have categories in the first column, dates in the second column und values in the third.

I would now like to get the values from the second table that coincide with the categories. And in addition the dates should also be equal to the date in the first table OR the date from the second table should be as close as possible but BEFORE the date in the first table.

Example

Table1

Red 07.08.2005 ???

Table 2

Red 18.04.2005 three

Blue 11.06.2005 one

Red 06.08.2005 four

In this example the value that should be looked up is "four".

View 12 Replies
View Related
Dec 8, 2013

Several places on my spread sheets I need to compare an action date to today's date.

I use: [Code] .....

in one cell and the fomulas to compare a date in a different cell to that cell.

I get the message "Circular References" for the cell the the above "today()" formula.

I do not get circular references in the cell that refer to the today cell, just the today cell.

View 4 Replies
View Related
Jun 8, 2008

I am making a spreadsheet that requires multiple dates and percentages. Is there any way that I can make the percentage that is related to the most recent date weighted more than a percentage with a date from a month or two ago?

For example:

X//Y//DATE

90%//85%//3-March

85%//70%//21-May

87%//90%//6-June

Is there formula so that I can have the 90% and the 87% be the most important (or weighted), the 70% and the 85% be the second most important, and the 85% and the 90% be the least important when I average them out?

View 14 Replies
View Related
Aug 10, 2013

I'm using the following formula which is entered by using VBA on the worksheet change event but the cell reference 'A2' is not changing for each row, i.e. A3, A4 etc.....

Code:

=MAX(IF( 'Device Use - 4 month Period'!$A$2:$A$20000=A2, 'Device Use - 4 month Period'!$C$2:$C$20000))

View 4 Replies
View Related
Jun 25, 2014

I have this formula counting down days until my next fire drill:

[Code].....

However... Every time the drill is done I need to change the date in the formula for the countdown. That I can have it reference a cell instead?

My initial idea was a formula like this:

[Code] .....

(however this code doesn't actually work ...).

View 5 Replies
View Related
May 18, 2014

I have attached an example spreadsheet which should be relatively intuitive over me trying to explain it.

I am wanting to extract all CJ numbers with the corresponding dates from Sheet 1, into Sheet 2. It has to factor in, engineer name (as stated on sheet 2), the Month (as stated in sheet 2 "May") with the "Attended site" date in sheet 1....

If you see the worksheet it should give you the whole picture with a fair amount of clarity.

Book1.xlsxâ€Ž

View 11 Replies
View Related
Jun 11, 2009

The spreadsheet attached is a timesheet I use. The last column on the sheet (IV) keeps a running total of time spent on each project. I need a way that i can find out how much time was spent on projects in specific timeframes (eg 01/01/09 - 07/01/01, or 01/01/09 - 31/01/09). my initial thought is perhaps i can have a "from" cell and a "to" cell to input the dates i require and it will total the relevant cells in the rows below.

NOTE! This workbook uses VBA to auto hide any column which are dates previous to today's date so you may need to unhide them to view times logged.

View 2 Replies
View Related
Aug 29, 2013

I have the below code to get the difference between todays date and the date located on sheet "requests" "B3". The code returns the value: -28040498

Code:

Dim MyODateRng As String

Dim MyODate As String

MyODateRng = Sheets("Requests").Range("B3").Value

[Code]....

So i gues with the first code there is something wrong with the date formating. But i do not understand why the second code does not return simply return the value: 1.

View 7 Replies
View Related
Apr 3, 2014

I have a workbook that is pulling data for every hour of the day from an internal website. The macro is built to pull data 24 times (each hour)

ex.(http://url/[""2014/04/01""]/starthour0/endhour1/)

(http://url/[""2014/04/01""]/starthour1/endhour2/)

(http://url/[""2014/04/01""]/starthour2/endhour3/) and so on.

What I am trying to do is set up a parameter that will reference a certain cell (Master!K5) which will contain the date I need to pull. I want to be able to have that cell referenced automatically and input the date for each URL in the macro.

View 2 Replies
View Related
Apr 27, 2009

I'm trying to calculate Interest accrued from a specific date. The problem is payments can be interest only which when used should keep the next months interest at the same rate instead of decreasing because the principal hasn't been paid down.

Im having problems when over the course of a number of payments they are principal + Interest and Interest only which requires at times to add up the same interest amount for 2-3 months in a row, then add up a different interest rate, etc. Additionally if the reference date is say on the 15th and the interest date is the 1st then for the last month I need to calculate the interest on a daily basis based on the current interest rate for that month.

The reference cell is H5, the Date ranges are J2 - J12, and the principal fields are: K2 - K12.

In the next post I will include the Amortization schedule.

In this example the interest is accrued at the same rate months 1-7, then decreased for months 8 and 9. so the total interest accrued should be eqaual to: $7487.74 (see next post for schedule).

Microsoft Excel - ZZ_Loan.Calculator-Update.1.1.xls___Running: 12.0 : OS = (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutH6H7H8H9H10H12H13H14=FGHIJKLM1 v 1.0 PaymentApply DatePrincipal + InterestAdditional PrincipalInterest Only2 1/1/2009 833.333 2/1/2009 833.334Quarter Close Backward 3/1/2009 833.335 Close Date9/1/2009 4/1/2009 833.336 Days of Interest0 5/1/2009 833.337 Beg Principal Balance$100,000.00 6/1/2009 833.338 Paydowns$980.41 7/1/20091,321.51 9 End Principal Balance$99,019.59 8/1/20091,321.51 10 Net Principle Due$3,562.50 9/1/2009 829.2711 Interest Due & Accrued 10/1/20091,321.51 12 Interest Earned (cummulative)$7,350.66 11/1/20091,321.51 13 Interest pymts (cummulative)$7,350.66 14 Net Interest Receivable$0.00 TEST [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

View 10 Replies
View Related
Jan 28, 2010

I have a spreadsheet set with the days in sach column (eg 29-Jan-09, 30-Jan-09, 31 Jan-09, 1 Feb-09 etc). What I would like to do is hide all columns prior to a date which is entered into a cell/macro text box.

View 9 Replies
View Related
Jul 25, 2013

Sheet1 is a report that expresses 90 days of information and Sheet2 has three columns of 365 day information. Sheet1 has a title, a today function for the date (cell B2), two columns (A5:A94 and B5:B94), and then some other info not relevant to the problem. Sheet2 has three columns (A1:A365, B1:B365, and C1:C365) with the date in column A starting at the first of the year and then two lists of information in columns B and C. I am wondering if there is any way excel would be able to recognize the Date (B2) on Sheet1 and be able to pull the information from Sheet2 starting on that date for the next 90 days.

For example:

My report always starts on the day I am accessing it. So... Say it is 1/15/13. My today function in Sheet1 (B2) would express 1/15/13. This date would be equal to A15 on Sheet2. So I would want A5 & B5 on Sheet1 to return B15 & C15 from Sheet2 respectively.

A5 Sheet1 = B15 Sheet2 & B5 Sheet1 = C15 Sheet2

A6 Sheet1 = B16 Sheet2 & B6 Sheet1 = C16 Sheet2

...

A93 Sheet1 = B103 Sheet2 & B93 Sheet1 = C103 Sheet2

A94 Sheet1 = B104 Sheet2 & B94 Sheet1 = C104 Sheet2

With that being said, the next time I open up my report, say a week from the 15th or 1/22/13 (expressed by my today function in B2), I would want A5:A94 & B5:B94 to reference the new set of information:

A5 Sheet1 = B22 Sheet2 & B5 Sheet1 = C22 Sheet2

A6 Sheet1 = B23 Sheet2 & B6 Sheet1 = C23 Sheet2

...

A93 Sheet1 = B110 Sheet2 & B93 Sheet1 = C110 Sheet2

A94 Sheet1 = B111 Sheet2 & B94 Sheet1 = C111 Sheet2

Is this at all possible?

View 8 Replies
View Related
Jun 19, 2014

I'm trying to make a vba that would save the file with the end date that will reference to a certain cell that user selects it. Working Sheet is a name of a File, and Current Sheet is a name of the panel where user will select the date.

ActiveWorkbook.SaveAs Filename:= WorkingSheet("Control Sheet").Range("D3").Value & Format(Date, "yyyymm") & ".xlsm", FileFormat:=51

But here i seem to missing the location

And I tried this one:

ActiveWorkbook.SaveAs Filename:="X:Work2014WorkingSheet& Format(Date,"yyyymm"). & ".xlsm" _, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

but it keeps popping up as 'expected end of statement' for yyymm?

View 8 Replies
View Related
Mar 2, 2007

I download & import CSV files/ sheets from bank, visa accts etc & I am trying to automate the processes I use including ensuring I (& others) will use the same naming conventions eg visa "yymm" = V0701, Direct Cedits = DC0701 etc. I am having trouble renaming or ensuring the active sheet is named according to the date in A3 eg in the example I want the sheet to be renamed V0702 as the date in the cell is 02/02/07 "dd/mm/yy" format ( imperial system). In BOLD is what I have & the renaming is messy becuase I searched the forum, Walkenbach's bible & help menu.

Sub VisaMonthlyAcct()

Dim shtName As String

Dim newName As String

ActiveSheet.Activate

'ActiveSheet.Name = shtName

newName = "V" & Format(DateValue("A3"), "yymm")

'Old name for future reference = Visa0701 (2)

ActiveSheet.Name = newName

Worksheets(shtName).Name = "V" & Format(Range("A3"), yymm)

'Worksheets("Visa0701 (2)").Name = "V" & (FormatDateTime(Range("A3"), yymm))

'To select Date column & format

Range("A3:A66").Selection.NumberFormat = "dd".............

View 2 Replies
View Related
Jun 24, 2008

I am currently working on a data analysis project (data mining) and need to collect and later analyze statistics for the inputs which control a series of calculations. These statistics are shown in the Statistics 1, Statistics 2 and Statistics 3 cells in the workbook that I attached. The inputs are X,Y; all possible values for these inputs are listed in the N,O columns. Basically I need a macro which would take the values from these two columns and place them pair after pair into the controlling cells (K3, L3), then it would copy cells H2 through L3 (updated stats) to a new sheet after each copy operation - so that I will finally have a list of statistics for all of the input pairs.

View 3 Replies
View Related