Cell B1 contains a date, then B2 contains a formula that says:
=IF(A1>TODAY(),A1,A2)
A1 contains a green tick and A2 contains a red cross.
What I am trying to add is that if B1 contains no date then B2 needs to be blank.
I tried using =IF((A1="",0),(A1>TODAY(),A1,A2) to get it to show a 0 if there was no data but this doesn't work.
I am using Excel 2003.
I need a function to work out what the date will be 45 WORKING days after today(), this function needs to exclude Saturdays, Sundays and any Public Holidays i.e. there could be either 10 or 12 weekend days added into the calculation depending on when today() is plus any additional Public Holidays.
I am using Excel 2003 although it will need to work in Excel 2010 shortly.
I need a formula that will calculate the number of days from a date entered into cell A1 to today's date. Whether it's before or after todays date. Example:
5/10/2009 to today is -9
5/22/2009 to today is 3
I am using Excel 2003 at work.
New cases are entered on to the spreadsheet. The case remains open until a closure date is entered, at which point the case is closed. What I need is the following:
Cell A3 = should say "Open" if there is no value in cell Y3
Cell A3 = should say "Closed" if a date or any value is entered in cell Y3. Ideally it should only say "Closed" if a date is entered in format xx/xx/xx, but am flexible so that it says "Closed" if any data is input.
At the moment Cell A1 has a drop down list consisting of open and closed. I will remove this if it causes complication.
I have a formula
=AL260-WEEKDAY(AL260,2)+MATCH(AP260,{"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday"},0)
The formula looks at the chosen start date (AL260), and then uses a displayed text day (AP20) to display the relvent date to the text day, this works fine however if the start date is say 08/08/14 (a Friday) when the formula detects a Monday (this copies down a range looking at the start date and the text days to produce dates on each line, the formula displays the Monday dates prior to start date, is there any way to alter this to make it display the dates ahead of the chosen start date?
I have a cell (A1) which contains the month. A2 contains the year. I need a formula which will return each date of the month.
View 7 Replies View RelatedThe following formula works perfectly in Excel 2003:
=SUMPRODUCT((A2:A10="A")*(b2:b10="B")*(c2:c10="C"))
However, I've got a fourth column that contains either a date or a blank cells; I want to limit the formula to only include certain dates in the sum. i.e:
=SUMPRODUCT((A2:A1000="A")*(b2:b10="B")*(c2:c10="C")*(d2:d10>="01/04/2014"))
Rather than an error it's just retuning an incorrect number.
Having a hard time putting this one together..Trying to do: Create a formula that counts how many cells in Column L, that fall within a date range and also have a specific category of "text" (Column E). What I've tried:=COUNTIF($L$4:$L$166,"
View 5 Replies View RelatedI have data going in to a small table which has some empty rows as that data is not yet available... My problem is, I need to sort this table in date order but with the date nearest to today's date at the top...
The sort function puts oldest at the top or oldest at the bottom which is no good for what I need...
I use xl 2003.
how to write a formula that will leave a cell blank if nothing is entered. I do not want it to show a 0 unless the cell entered is a 0.
In cell G16 - I am adding U46 and U58 together.
I do not have a problem when it is 1 cell - my formula works fine. When I have 2 cells added together, the formula does not work.
this is my first post and i was a little unsure as to whether to put this in the General or VB/Macros forum, because it kind of involves both.
i'm trying to write a macro that inserts a formula that uses the date of the day that it was run (that is, i don't want it to be volatile like TODAY() and NOW(), but i don't want to have to manually type in the date into each formula).
is there a way that i can write a formula that uses the date of the day it is entered into the cell, or write a macro that adds today's date (perhaps using ActiveCell.Value = Date) and then writes a formula around that?
I cells B5:B15 I have a list of dates from last year. I need a formula that I can put in cell D5 that gives me the date that is closest to the same day last year.
Example:
If today is 4/9/14
and
From B5:B15 there were the following dates:
3/12/13
3/19/13
3/29/13
4/5/13
4/8/13
4/12/13
5/6/13
5/29/13
Cell D5 would contain
4/8/13 since it is the date closet to same day last year.
Essentially I have three columns.
Column 1 is the name of a property
Column 2 either says "Primary" or "Secondary"
Column 3 has a date of when some building work is due to be completed.
I need a formula that can tell me how many entries there are in the second column of "primary" sites that have a completion date that is less than the current date.
I tried searching through the forums, but I don't exactly know how to word my question!
I have a workbook with two sheets: Meetings and MasterStatus
On the both sheets I have taskID for a specific task.
On the MasterStatus sheet, I want to use an array to look up the next meeting date for each taskID (Column C), referencing the Meetings sheet (Column E) to do so.
The formula I have so far doesn't work:
=MIN(
IF(
AND(
Meetings!$A$2:$A$3000>TODAY()-1,
NOT(ISBLANK(Meetings!$A$2:$A$3000)),
Meetings!$E$2:$E$3000=$C$7),
Meetings!$A$2:$A$3000,
0
)
)
I’ve been searching the forum but am struggling to find exactly the information I need!
I’m trying to get a column of cells to update with the date that the cells contents change to “Y”. I had been using the formula =IF(I7="Y",TODAY(),"-") in cell J7 but this updates the date every day. I need this date to remain the same as when it’s first populated.
I’ve been trying to cut and paste text from existing posts into the Visual Basic code but am new to this so am not getting the results I need. I had tried:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'will put date in column B when something is put in A
If Target.Column = 9 Then
Target.Offset(0, 1).Value = Date
End If
End Sub
But this caused all sorts of problems! I then tried messing around with:
Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Column = "I" Then
If Target.Value = "Y" Then
Excel.Range("J").Value = Date
End If
End If
enditall:
Application.EnableEvents = True
End Sub
But this doesn’t work either. In fact, both these codes are probably riddled with errors as I’ve been trying to learn by trial and error!
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)
I have a workbook that I'm using to tracking staffing patterns within a mental health agency. When the workbook opens the user is asked to pick a date range and an office location. I've placed code into the userform that pre-fills the "start date" with today's date and the "end date" 7 days from today's date. I would like the user to be able to enter a unique date range should they wish but I have yet to figure out the coding to accomplish my goal.
[Code] .....
Attached File : Staffing Report 1.90.xlsm‎
I am trying to get the results of the number of days between today and a future date. I am using ="cell containing futuredate"-today() and it gets me the correct number of days. The problem comes in when I have yet to populate the future dates. I am getting -39991 (numeric value between today and jan 01 01) and because I am also using conditional formatting this is even more of a problem. Is there a way get excel to display nothing if it is a negative number? or to give a specified resut if the number becomes negative such as Expired or something of that nature?
View 3 Replies View RelatedTrying 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
I have a worksheet that has a sent date and expected delivery date I need create a macro that will alert me if today's date is within 5 days of expected delivery date.
View 14 Replies View RelatedI am trying to lock cells after today's date has passed so that no one can make changes to it after today's date has lapsed. This is for protective reasons so that people do not remove their names from reserving something after using it. Now the code should disallow locking after cell input entry when today's date hasn't passed so that changes can still be made by the user. I am trying to determine the code to do this but I have no idea as to how to do it.
Here's a scenario: I reserve something for Aprill 11, 2009. I input my name. Since it's April 9th, 2009, I am still able to make changes up and until April 11, 2009. After this date, the cell is locked and no changes can be made, except for the administrator.
Trying to automate the period part of the impt function
To calculate current value of loan i have the below formula below with the 3 being the current period
=IPMT(4.3%/12,3,5*12,-7000)/(4.3%/12)
What i would like to do is for the period to be self calcuating from current date and the loan start date. I can return a value in days using start date - today() and aware month function returns the month number but stuggling to find a way to work out cumulative month from the start date.
is it possible to display the week number of todays date (today()) from a physically entered start date (which would obviously be week one), the start date would be november 4th 2013.
View 3 Replies View RelatedOn A3: D10 I have information on C1 a have date and E3:E10 I will input date
i.e. If the date entered in E3 is the same as or larger than the date on C1 then Blank the entire line A3: D3
If a1,a21,a41 have a value greater than 0, I wish to stamp cell b1,b21,b41 and so on with todays date, but without the stamped value changing/advancing tomorrow. At the moment cells b1,b21,b41 are copied down as follows. =if(a1>0,TODAY(),"") This works fine, but the date stamp of course changes tomorrow. I can not apply code to the whole b column as cells b2:b20, b22:40 etc have other non-date format data to which the code does not need to apply - the todays date value cell occurs every 20 cells.
View 6 Replies View RelatedI have a large spreadsheet with a long date range from A:3 to A:900
what would be the best way to jump to today's date? I have seen formulas, Macro's, Active X controls, Hyperlinks?
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?
how can you automatic the date to today's date when you open the excel file?
ie.
Price Report For 02/25/09
I am working on a spreadsheet to create a school 'tracking' system based on excel. What I need is that as soon as a box in column B is ticked, a date (TODAY() is entered in column A. That is not so difficult, and I have used the formula in cells in column A:
=IF(ISBLANK(Bx);"";TODAY()), where x is the specific row.
So, keep cell A clear untill something is written in cell B. This works fine.
But, for the purpose I need to keep the TODAY() date fixed after entry. And it need to be erased again if the tickbox in cell B is emptied again. It need then to re-enter a new date after cell B is re-used.
I am setting up some validation on my userform. In this case if a user enters a date within two weeks of todays date they will not be able to proceed onto the next page.
Example: Todays date is 19/03/14. If a user enters 25/03/14 into 'TxtDate' they will not be able to proceed.
[Code] ......
