Track & Record Dates, Past And Actual
Mar 8, 2008
I am creating a tracker to record, incoming, latest date of reply needed and actual date of reply. So I have column A= received, Column B = need to reply by, Column C = replied on. What I want in A is today’s date which is easy enough with ctrl+; is there a way of having it set that as soon as the cell is clicked on that date is activated and until then it remains empty?
What I have in B is A# + 20 this adds 20 days to the date entered in column A, again is there any way to have it set that this automatically activates when cells in column A are active and not until then, as if I enter that code in the cell and nothing is showing in cell A it gives a date of the 20/01/2008.
In C I have tried a couple of codes and they have not worked so what I would like is a code that when A and B are active it generates a countdown for days left to reply and if this countdown reaches zero then the whole line is flagged up in red as late. Lastly, is there a code that I can use that will automatically correspond with the A, B or C columns and match them with the same number cell in the other columns, sorry this isn’t so easy to explain but what I have to do at the moment is type in B is =A1+20 , A2+20, A3+20 and so on rather than just typing in A……+20 for column B to know it needs to associate itself with the same number cell in column A regardless what number it is.
View 2 Replies
ADVERTISEMENT
Apr 5, 2009
The spreadsheet i am working on requires mileage between last trip and previous trip.
Unfortunately there will be blank cells between as shown below ...
View 9 Replies
View Related
Dec 19, 2008
My sheet autosorts based on the total sum in column d, when i put data in column e,f,g,and h.I am trying to have column b track data for each item(with +/-)based on what row it was on from previous week.
View 4 Replies
View Related
Sep 18, 2007
Sheet A1= Sheet!B1, Sheet!B1 data keep on changing - I want to track Sheet A1 data in the same sheet with time stamp and column wise.
View 5 Replies
View Related
Jun 19, 2008
I am using the following track changes code on a worksheet;
Track/Report User Changes on an Excel Worksheet/Workbook
in the this Workbook. It involves two different VBA solutions I had gotten form Ozgrid. The top part is VBA code to track changes in the workbook, THe instructions are to put the statement at the top of the module which I did. When it gets to the second VBA code {Starting with Option Explicit} below, I get an error message that "Only comments may appear after End Sub, End Function or End Property.
Also, I would like to get the VBA course offered on this website, any comments?
Dim vOldVal 'Must be at top of module
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim bBold As Boolean
If Target.Cells.Count > 1 Then Exit Sub
On Error Resume Next
View 4 Replies
View Related
Mar 9, 2008
I currently have numerous workbooks with linked cells to a master workbook, but the linked cells dont appear to carry over or retain the formating from the master workbook (e.g. font style, bold, underlined, font color, cell color). I make changes quite often to the master workbook and would like the changes carried over to the other workbooks. Does anyone have a script where it would automatically carry over the font formatting? I hope this is understandable.
View 4 Replies
View Related
Oct 15, 2009
I get a spreadsheet every day that has a column of numbers that contains dates that are not in a date format. Is there a way to convert these numbers to a date format? The numbers come from an outside source so I can't modify on the front end. look at the attached spreadsheet with representative data of what I'm trying to ccomplish.
View 2 Replies
View Related
Jul 29, 2008
I want to take a date (ex. May 25, 08 is in cell A2) and calculate 30 days (A2+30)June 24, 08(A6) and what i am trying to due is calculate if it is past due.
I used the if command but it does not seem to be calcualting the date plus thirty, it is only taking the date that is listed. (ex. instead of calculating =IF("A2+30"
View 9 Replies
View Related
Aug 18, 2007
to set a conditional format and include a function code in it as well?
I want a spreadsheet to change the color/font of a cell/column based on a comparison of a "start date" cell and a "due date" cell. This is to automate the process of highlighting items that have gone past the due date in the second cell. I was trying to do this by looking at the "now()" function and comparing it to the due date.
View 9 Replies
View Related
Aug 20, 2007
I am trying to create a spread sheet in excel that will show a number of days past due counting from the due dates. see the attached xls file for details.
View 5 Replies
View Related
Mar 10, 2014
I have a simple table for tracking performance evaluations with 3 columns:
Employee Name (A), Date of Hire (B), Eval on File (C)
Evaluations are completed 6 months from Date of Hire (1 time) and on an annual basis and logged into column C.
I need to identify a list of evaluations that 30 days past due from the beginning of each month on a monthly basis.
I spent hours breaking the process down into steps (by adding extra columns to calculate 6 month due date, difference between current date and 6 month, due date for current year, and it still takes a long long long time.
There must be an elegant formula there that can tie it all together. I worked hours to research it and failed.
I put a scenario below to illustrate my table. As of 02/01/14, Anna Li's 6 month evaluation is past due, James and Johns' annual evals are past due as well.
Employee NameDOH Perf Eval
Anna Li 6/8/2013No
James Hawkins12/12/200612/12/2012
John Silver 1/8/20071/8/2013
Kim Woo 8/14/20078/14/2013
Sam Shell 6/6/20056/6/2013
Tanya Smith 7/1/2013 1/1/2014
View 2 Replies
View Related
Feb 1, 2010
I need a formula that can count items that are past due by up to 30 days, 60, or 90 days. I'd guess the total of all 3 values should = the total past due.
I have the total past due as =COUNTIF(A!I$1:I$278, "<=" & TODAY() )
View 2 Replies
View Related
Aug 15, 2013
Through VBA I am trying to filter for today's date and all dates 30 days in the future as well as all dates 30 days in the past.
I am currently using the code below, but it only show dates 30 days in the future. I cannot get it to populate cells that are either 30 days in the future or 30 days in the past.
Rows("4:4").Select
Selection.AutoFilter
ActiveSheet.Range("$A$4:$HQ$1000").AutoFilter Field:=4, Criteria1:="TBD"
ActiveSheet.Range("$A$4:$FQ$1000").AutoFilter Field:=12, Criteria1:= _
">=" & Date, Operator:=xlAnd, Criteria2:="
View 2 Replies
View Related
Jan 23, 2014
I need to count the actual number of days between 2 dates but excluding Sunday...
For example,
Start Date-----End Date-------# of Days
01/Jan/14-----23/Jan/14-------20 days (excluding Sunday)
In some cases, Holiday also to be removed i.e. 01-Jan-2014 is holiday, then the # of days should be 19 days.
If I use Networkdays formula, then system results excluding both Saturday and Sunday. But I want to remove only Sunday.
View 5 Replies
View Related
Oct 17, 2013
I am trying to create three different conditional formats for my date tracker. I have attached an example of what I am trying to accomplish and tried to write out what the rule's are, just struggling on how to write the formulas.
Condition 1 - If the cell in column C's date is past due, change the cell red only if there isnt a date in the "Actual" column B
Condition 2 - If the cell in column C's date is coming up in the next 30s change the cell to yellow only if there isnt a date in the "Actual" column B.
Condition 3 - If there is a date in column A and Column B turn column C's cell green.
I am using this formula "=+IF(B3="",A3,B3)" to populate Column C.
Conditional Formatting.xlsx
View 1 Replies
View Related
Feb 14, 2014
I need creating a formula that combines the following if statements in cell C107:
=IF(AND(B107
View 1 Replies
View Related
May 2, 2014
I'm brand new to using formulas for conditional formatting. How to achieve the following...
Red highlight for todays date and past dates
Yellow highlight dates up to 30 days before today
No highlights for blank cells or text
I'm using excel 2010.
View 11 Replies
View Related
Jun 11, 2014
I have spent too many hours Googling and trial with mostly error in attempting this. I formatted the very first cell A1 for today's date =TODAY(); this is also dirrectly above the column with all the dates in it. I just want an entire row's contents to turn red if it is 15 days old or older. This =$A1<TODAY()-15 somewhat works, but it only turns the date cell red, not the whole row. It also makes blank cells turns red, which I would like to remain blank (another formula is needed I imagine).
View 3 Replies
View Related
Dec 7, 2005
I am trying to make a worksheet that will track the annual inspection dates for my equipment and basically change color when the inspections are comming due or are overdue. Im using conditional foramtting what would the formula be if i did an inspection on 6-sep-05 and i want excel to tell me when it is 5 days before the due date which would be 6-sep-06.
View 5 Replies
View Related
Apr 25, 2012
I have a spreadsheet with (to keep it simple) with three fields: Name, Date, Complete.
When the spreadsheet opens I'd like a macro to run to check each row of data, and if the Date is before or on today's date and the Complete field is empty, a message box should appear to alert the user with the Name and row number.
View 9 Replies
View Related
Mar 30, 2007
I want to maintain a history of exam results held on different dates
For this purpose I created a file with three sheets named Inputs, Working & result history. Description of all sheet is as follows :
“Inputs” Used as main Input file to feed marks for each subject on certain exam date.
“Working” Linked with sheet inputs and perform calculations ( total marks, % etc)
“Result History” Date wise history of exam results based on sheet (working).
1. Score entered for different subjects in Sheet “Inputs” for a particular exam date.
2. Obtained marks are totaled and score % calculated based on scores entered in sheet inputs.
3. Date wise history of exam results recorded based on working sheet.
To transfer/copy/paste results from “Working” sheet to “Result history” sheet I recorded a macro which is linked with a button. This macro copy record from working sheet and paste it as value to result history sheet.
Now I need modification in this macro/or any other solution so that:
when I entered scores for next examination date in “inputs” sheet all calculations done as mentioned above in step 2 (for earlier exam date) & when I click the button again it post the result details to “result history” sheet in the row next to first record.
In this way a history of results for different examination dates can be maintained.
View 9 Replies
View Related
May 25, 2007
I seek advice on using the value of NOW() as a record ID in an address book program. Question #1: Do Excel developers often use a record ID? Question #2: What record ID schemes are fequently employed besides date/time? I have decided to create an Excel address book as an exercise to increase my knowledge of VBA, and also as a useful application for work.
I realize that a record ID is not essential in Excel in the way that it is essential in Access, but I feel the need to have some unique ID associated with each address, so that I may have different worksheets, with data related to a given Contact, sort and manipulate it, if necessary, but have the record ID as a way to restore the relationship of rows to a given Contact, and also, as a handy way to examine the data in the date/time sequence in which it was entered. I have experimented with the following code, to assure myself that I can access the number returned by the NOW() function, manipulate it as a string, and format in various ways if necessary.
Dim n As Double
n = Now()
sn = Str(n)
p = InStr(sn, ".")
first = Left(sn, (p - 1))
l = Len(sn)
d = l - p
S = Mid(sn, (p + 1), d)....................
View 2 Replies
View Related
Mar 16, 2014
I am creating an asset management sheet. For the formula I am trying to work out there uses 3 fields : ID, start date, and end date.
What I want to do is be able to show if the ID is duplicated within another record with an overlapping date. So an item is flagged if it is in the list within the same dates as another record. I tried a few countif formulas but with no success.. I may just be approaching the problem incorrectly though.
View 1 Replies
View Related
May 26, 2009
I am looking to create a macro that will create a new sheet when data is added on a summary sheet. Example.
1. Summary sheet called "Variations" contains columns that will contain the information needed for new sheet (Columns A to D)
2. When data is entered on "Variations" sheet: Column B, then macro automatically creates new sheet renamed to e.g. VO1 (Number used on "Variations" tab) and is a copy of "Master" tab.
3. Data entered in Column A to D on "Variations" tab is automatically entered onto new sheet created (e.g VO1). Shown is blue on attached file. Additional data is updated on "VO1" sheet and this then links back to "Variations" tab
View 6 Replies
View Related
Mar 20, 2009
I'm trying to find a macro that will run allowing the user to select a 'starting record number' and a 'finishing record number' when printing.
I have a spreadsheet that feeds from a master list in excel, from over 5000 records.
I need to print the s'sheet with any given indivdual record's information at any given time.
Individual prints are fine. However if I wanted to print from record number 1500 to record number 3000 it would take me all day.
Is there a way I can set up a macro so an option form pops up? allowing selection of "From record" and "to Record" ?
View 13 Replies
View Related
Jan 6, 2007
Im trying to record a macro that will enter a formula in a cell each time I run the macro
Drop the Lowest2:
=IF(ISERROR(AVERAGE((SUM(C5:L5)-MIN(C5:L5)-SMALL(C5:L5,2))/(COUNT(C5:L5)-2))*Scale!$B$2*10),(IF(ISERROR(AVERAGE(C5:L5)*Scale!$B$2*10),"",AVERAGE(C5:L5)*Scale!$B$2*10)),AVERA GE((SUM(C5:L5)-MIN(C5:L5)-SMALL(C5:L5,2))/(COUNT(C5:L5)-2))*Scale!$B$2*10)
Drop Lowest1:
=IF(ISERROR(AVERAGE((SUM(C5:L5)-SMALL(C5:L5,1))/(COUNT(C5:L5)-1))*Scale!$B$2*10),(IF(ISERROR(AVERAGE(C5:L5)*Scale!$B$2*10),"",AVERAGE(C5:L5)*Scale!$B$2*10)),AVERA GE((SUM(C5:L5)-SMALL(C5:L5,1))/(COUNT(C5:L5)-1))*Scale!$B$2*10)
I know I can write the formula in a simpler way, but I was asked to make it like this.
The problem is that when I enter the formula in the cell while recording. I got a message: "Unable to record" so this part can't be recorded
View 14 Replies
View Related
Sep 1, 2009
This problem seemed to revolve around "digital signing" with further macro changes done from a different machine (without the proper certificate). Excel warns of the problem and then "unsigns" the project.
I've got a problem with spreadsheets that I've been working on. Now, when I tell it to record a macro, I get the macro name, shortcut assignment dialog box and then an error "Unable to record." After "Ok"ing that dialog box, the normal recording macro dialog comes up. Also, lo and behold, there will not be a macro recorded.
I've got no protected cells or sheets and it doesn't matter what security level is set. It is a "signed" macro but I can take off the signature and still have the problem. I checked for "Help - About" for deactivated modules and there was none.
View 2 Replies
View Related
May 17, 2013
I need this code to also add the formula to one past the last Row
Now it goes down column A and if its not null it puts a formula in column D. I need it to also put the formula in column D one row past the LastRow of Column A.
Dim A As Long
Dim lastrowA As Long
With Sheets("Children")
[Code].....
View 1 Replies
View Related
Apr 28, 2009
Here is what my table is im working with. The tite "todays date starts in Cell A1 and B1 is =Today().
I need to figure out how to do an if for the over 30,60,90 past due. Here the logic I need to figure out:
in the Over 30 column, the logic of the statement is as follows: If the difference between today's date and the date of the invoice is greater than 30 and equal to or less than 60, the amount goes into this column.(over 30 E3) To get the amount(the ammount in C3) into the Current column, the difference between the two dates must be less than 30. To get into the over 90 column, the difference between the two dates must be greater than 90. You can figure out the over sixty column. And this needs to be copied to the remaining colums
Today’s date: =TODAY() Shop Invoice Date Invoice Amount Current Over 30 Over 60 Over 90 Ludwig's Landromat =TODAY()-72 $432.78 Willie's Washing =TODAY()-107 $565.11 Wash It Here =TODAY()-80 $308.47 Spiffy Wash and Dry =TODAY()-58 $725.37 Water Soap & Hot Air =TODAY()-30 $427.95 Wash it & Dry it =TODAY()-90 $603.91 Acme Wash & Dry =TODAY()-60 $332.35 Totals
View 9 Replies
View Related
Sep 30, 2009
I am trying to create a function that will tell me when something is past due.
I have a excel document for my office's accounts payable, it has a master sheet where all of the companies that we pay are listed, and they are linked so that you can click on the name and it will take you right to the page to see a more detailed breakdown.
I am trying to figure out how to make something on the master page to tell me when something is due, even if it just makes an X when it is past due.
There are about 73 worksheets in this document and each worksheet contains many dates.
Is this even possible for me to do something like this? If not does anyone have any suggestions?
View 10 Replies
View Related