I need a formula to calculate how many months has passed irregardless how many days had passed. What I mean by that is if I have a starting date of 1/31/2010 and an end date of 2/1/28/2010, that should count as 1 month passed. I tried using the DATEDIF function, but that function is counting number of days, so if only 28 days had passed, that would not count as 1 month.
I have a formula which calculates number of days remaining. But I need a formula to to know how many days passed after a particular date.
Example :-
Date .Days passed
10-Feb-2010 .6
=IF(ISBLANK(G14),"",IF(TODAY() > G14,"LIC EXPIRED",G14-TODAY()))
I am trying to figure-out how to set up conditional formatting of dates in a column (e.g. Date Submitted) and have these dates, and/or cell, change color as time progresses. I have Excel 2007.
For instance I have an application submitted on 11/20/2011 in a cell. I would like to have the date change color (Yellow) after 30 days have passed.
Then do the same except change to Red after 60 days have passed the date.
I searched the forums and did not find this particular problem. I of course may have missed it too...
My guess was to try "$A$1
This might be a simple answer but... if A1 had the date 1-Jan-2010, what formula would I used, within conditional formatting, to make that cell turn blue once 90 has passed? So the cell should turn blue on 1-Apr-2010.
View 10 Replies View RelatedI am new to Excel programming, haven't done any before and not familiar with creating/editing macros. Using Excel 2007.
I would like to automatically lock all the cells in a row if the date in column A in that row is >= 7 days old. Data is entered once a week (some time Sunday - Saturday) and I would like historical data (rows) to be locked so that they can't be edited inadvertently.
If possible could I also highlight the current working week somehow?
I have attached the worksheet : testworksheet.xlsx
[data] ....
Here I have 9 total requirements to be tested in any event. If I wanted to count the number of events passed it wouldn't be 7. See Blah2 it passed Design and Electrical. Separate Events yes, but only one requirement. I need to have a total could of 1 for that as passed. Not two. I'm comfusing myself because this inherited spreadsheet is over 1,000 requirements.
I am using this formula to calculate a set of PH(Public Holidays) that have passed from a period of 1/4/2009 to 31/3/2010.
=IF(TODAY()>='Team Schedule'!D5,1.5,0)+IF(TODAY()>='Team Schedule'!D6,1.5,0)+IF(TODAY()>='Team Schedule'!D7,1.5,0)+IF(TODAY()>='Team Schedule'!D8,1.5,0)+IF(TODAY()>='Team Schedule'!D9,1.5,0)+IF(TODAY()>='Team Schedule'!D10,1.5,0)+IF(TODAY()>='Team Schedule'!D11,1.5,0)+IF(TODAY()>='Team Schedule'!D12,1.5,0)+IF(TODAY()>='Team Schedule'!H2,1.5,0)+IF(TODAY()>='Team Schedule'!H3,1.5,0)+IF(TODAY()>='Team Schedule'!H4,1.5,0)-4.5
From what u can see here, its a massive formula.... but I am pretty sure that there is a shorter way to key in the formula
*Note*
'Team Schedule'!D5-D12 and 'Team Schedule'!H2-H4 are PH. disregard the 4.5 that is in the formula as it is used for other purposes. Every PH that has passed will credit a 1.5 into the cell.
Is there a way to count the times a loop has occurred and be able to present a running total in the status bar of MS Excel?
Code:
Sub Master_Phone()
'
' Select cell A2, *first line of data*.
[Code].....
Private Sub Workbook_Activate()
Dim user As String
If Worksheets("Setup"). Range("D6").Value = "" Then
user$ = InputBox("Hello. Please enter your name to inialize the program", "Enter Name")
Worksheets("Setup").Range("D6").Value = user
MsgBox ("Welcome " & user & ". Press 'OK' to continue on to the Main Menu.")
Else
user$ = Worksheets("Setup").Range("D6").Value
MsgBox ("Welcome back " & user & ". Press 'OK' to continue on to the Main Menu.")
End If
Exit Sub
but i would like to expand the "welcome back" msgbox to also display "the last time you were was was (eg: 2 days/3 hrs/14 min) ago" - which i presume would be calculated from the last save.
I want to highlight the rows in my worksheet when the dates in column 'N' are in the past. I've seen codes to do this using conditional formatting when searching online and in here but the problem is is that it highlights blank cells as well. Is it possible to correct this?
View 8 Replies View RelatedI would like to know a way to sum the future months dollars only once the month has passed to consider that amount only in my forecast. For eg. If I have a Vendor A contract from Jan - April for $1000/per month in total for $4000. My Forecast should only be Feb-April = $3000. So my total column should only display $3000. Once Feb has passed , the forecast should only be March-April i.e $2000. How to get rolling month sum of forecast once month has passed.
Attached is a sample spreadsheet with different vendors.
Rolling sum after month has passed.xlsx
My original formula is:
=IF(ISBLANK(VLOOKUP($E39,'Players Scores'!$A$4:$AV$700,'Players Scores'!AA$3+6,FALSE)) = FALSE,VLOOKUP($E39,'Players Scores'!$A$4:$AV$700,'Players Scores'!AA$3+6,$E39),"")
I am using the above formula but would like it to get the value when the date has passed (when date has been inputed in another cell)
I have added an attachment, The formula begins at J44 to AY44 but when date has entered in the red (D44:D67)section I would like to retreive values for blue section(Q44 and onwards) and not the yellow section(J44 to P44)
This is so when players make a transfer it doesnt include the weeks before that date!!
What formula would I use to populate a cell?
Example
e13 = 0 until after the 10th of the month on the 11th it = $100.00
If I13 has an amount in it on or before the 10th e13 remains $0.
Each of the 12 line of this rent roll needs to correspond to its own month.
I have a worksheet that has a drop down box with the options PPP, MONTHLY, TOKEN & DEFAULT, next to this i have a date when a payment is expected, what i want to do is automaticaly set this to default after the date has expired, the problem i have is that there is data validatrion in this box so i cant enter a formula, is there a way around this
The following afterupdate procedure for my txtStart1 text box sends the value to the FormatTimeValue function that is coded in a module.
Private Sub txtStart1_AfterUpdate()
MsgBox Len(Trim(Me.txtStart1.vaue)) '<= outputs 1
Me.txtStart1.Value = FormatTimeValue(Trim(Me.txtStart1.Value))
End Sub
As you can see from the bolded text, the length of the value is 1 when outputted from the forms code.
However, when I pass it to the following function in a module it outputs 2 as the length.
Function FormatTimeValue(vTarget As Integer) As String
Dim TimeStr As String
If IsNumeric(vTarget) Then
MsgBox Len(vTarget) '<= outputs 2
Select Case Len(vTarget)
Case 1 ' e.g., user entered 1 so time should be 01:00
TimeStr = "0" & vTarget & ":00"
All the subs below take place in Module4. I have a variable, MoreDates, defined as Boolean. At first it was just dimmed, now I have it as Public. When it's passed to a sub, it's true; in the called sub it's changed to false (and I see it as false when stepping through) and sub is exited. The next line of the calling routine checks if true/false...but it's true again. I'm not passing by value, so I don't know why this is happening.
MoreDates=True
Do Until MoreDates = False
Query_BigCharts (MoreDates) '***************
If MoreDates Then
temprow = temprow + 1
Range("A" & temprow).Select
lastdate = DateAdd("m", -1, lastdate) ' backs last-written date up a month
Range("D6").Value = lastdate 'send to eom cell
lastdate = Range("D8").Value ' formula in d8 converts to last weekday of the month.
End If
If lastdate < FromDate Then
MoreDates = False
End If
Loop
End Sub.............
In my excel sheet, I have date values stored in cells from D7 ti IV7. in cells C8 to C100, I have a data validation which selects the values from cells D7 to IV7. When user selects a particular date in coumns C8 from the list, using my code, I am trying to search for the value stored in cell C8 within the range D7:IV7 using function Application.Match
While running the code by clicking on "Distribute Budgeted Efforts" command button, I am getting an error "Type Mismatch" and error is coming from code line number 27 which is "lInitial = Application.Match(lInitialdate, Range("D7:IV7"), 0) - 1"
My code is as below
Private Sub cmdEffortDistribute_Click()
Dim lCount As Single
Dim lStartDate As Date
Dim lCounter As Single
Dim lBudget As Single
Dim lInitial As String
Dim lInitialdate As String
Dim lInitialWeek As String
I am also attaching excel sheet which I am using here for reference.
If C2 = 21/06/14
I need F2 to highlight when 60 days have passed from the date entered in C2.
Would this work if placed in F2 =C2+60<TODAY()
No access to excel at the minute to try it
I have a query where I am trying to find out how many people passed in green, amber and red from a series of data. I know (from the attached file) how to find out how many greens, ambers and reds there were, but how can I find out how many of the 7 in cell B12 passed in Green?
View 2 Replies View RelatedI am trying to devise a service schedule that has the installation date in say cell D4 and I would like this to automatically place a date a year from there in to cell E4, then 4 weeks before date comes round i would like the text cell to turn yellow, then on & after the date to turn red. Is this even possible?
View 12 Replies View RelatedI am working on a sheet that has over 150 tabs of information. It is a simple audit that allows for Yes, No or N/A to be selected for each audit category. I have an Outcome Summary tab that is pulling totals for all the audit information on each tab. I did a CountA statement to count any cells (from all worksheets) that had anything selected, so I have my count for the amount of files audited, but for the life of me I cannot come up with a formula that counts the ones that passed or failed the audit. Needs a formula that counts for Yes as 1, No as 0, N/A as 1 (these don't count against us) and doesn't return an error for a tab that wasn't selected.
View 3 Replies View RelatedI want to make a sheet that will lock cells from rows that have passed the yesterday date. In A column i entred the days and the locked row must have the range from A to U. After i read the post from here Lock / Protect Rows Past Due Date any solution.
View 3 Replies View RelatedHave a macro where is some dates have passed the formulas are cleared but if the date is < than TODAY the value is left. All worked ok until I changed to formula to where the date calulated exclued weekends. Have attached example spreadsheet.
Forgot to add the marco was provided by the hard work of fellow Ozgrid memeber (Ger).
how to display the result i made in macro to active worksheet so that i can keep a record of the result made in my random generator,
Sub timer()
TextBox1 = ""
TextBox2 = ""
TextBox3 = ""
TextBox4 = ""
Dim x As String, i As Byte
Randomize
x = Format$(Int(Rnd * 1899) + 1, "0000")
For i = 1 To Len(x)
Me.Controls("TextBox" & i).Value = Mid$(x, i, 1)
Application.Wait Now + TimeValue("00:00:03")
Next
End Sub
Private Sub CommandButton1_Click()
timer
End Sub
Private Sub TextBox1_Change()
End Sub
I 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.
In column A of a database I've calculated workdays for all year (excluding weekends and public holidays).
Every day, I update the numerical data of the database, with the facts of previous day.
In another sheet I've made some monthly, quarterly etc., reports for that data.
What I want, is to have:
1) a report every day, with the sum of specific data (say that in column D), for a period starting the first workday of current month and ending the previous workday of today.
2) the total workdays days for that period.
The code as it stands allows me to push a button and it immediately picks up on a meeting category and date of meeting, then sends emails to those that are supposed to attend based on a date that is within 7 days from the current date of pressing that button. HOWEVER; it seems to still send the email when the date has passed...is there a line I can put in this code to make it so that every date that has already passed will be discounted from future emails?
The code is as follows:
[Code].....
I would like to develop a user-friendly Excel 2010 spreadsheet that would allow the user to enter from 1 to 10 (i just picked this number at random just to have an upper limit) employee numbers in cells A1-A10 and "click" on the VBA Code button that would invoke an Access Query and in the process return all the applicable data gathered from the query back into the Excel Spreadsheet. Believe it or not, I can do all this mentioned so far. BUT what i cant do is my second option for the user which is allow them to pull ALL of the employees back into the spreadsheet using the same query.
In summary, using Excel as a frontend dashboard, I know how to get a specific number of employees' information from a query and I know how to get all employees' information from a query but I dont know how to get either/or. In other words, I could do this with two queries and two "VBA-Code" buttons but I would like to do it with one query and one button.
I have a protected worksheet. Users wish to be able to track changes in the input cells. The suggested approach for this is to temporarily disable sheet protection and allow them to change the font color, then protect afterwards. What I would like to do is:
i) check whether they are in an input cell
ii) if so, then prompt the user with the 'Font Color' dialog box
iii) apply the font color selected to the input cell
I'm struggling to find the dialog box I need. I can launch the one to change the interior color, no problem (Application.Dialogs(xlDialogPatterns).Show). But that's no use to me, I just want a color palette that specifically relates to the Font Color
How can I convert the number of days for example 366 days to years, months, days 366=1year, 0 months, 1 day
View 3 Replies View Related
