I want to change the date on my spreadsheet which runs from May 07 to Apr 08 by just inserting one cell, how do I complete this action. The months are in seperate cells.
May-07Jun-07Jul-07Aug-07Sep-07Oct-07Nov-07Dec-07Jan-08Feb-08Mar-08Apr-08
I am using a worksheet change event to display the date and time that certain cells are manually changed. However, I would also like this to happen when a cell displaying a value based on a formula changes.
View 1 Replies View RelatedI am trying to put together a report tracking sheet, ie I have different test that take a different length of time to return to the office and I want to be able to see when a test should be back from the lab,ie:
A blood sample takes 1 day, a urine sample takes 3 days.
I need a formula that basically says that if the data in cell A1 = Blood, (and I have the test date in cell A2) then the data in cell A3 should says A1=Blood, A3=A2+1 to give me the test due date
I found the following formula in one of the other posts;
=IF(E5="BLOOD",G5+1,IF(E5="URINE",G5+3,0))
The problem that this will only work with two tests and I have more than two tests, I have fourteen and each takes a different length of time to process.
I'm trying to make a summary page that will provide a monthly report based on the data in a second sheet.
I have attached an example with dummy data.
I know this could be easily done with a pivot table but the page format has to remain static and reflect current practises.
At the moment I can only return data from the whole range - not just the desired month.
Fruit Example.xlsx
Just wondering if there is a formula where I can change the date an employee started working into the number of months employed??
Example, John Smith started here in January 2011. I would like the formula to return 8 months.
I'm trying to create a list that references an existing data set where I have staff listed month by month and based on today's date, imports only if there is data in that column. I have figured out how to check if the cell is blank or not, but what I want to do now is change the cell that is referenced in the formula based on the date. Here is the format of the spreadsheet I'm working with:
A
B
C
D
E
F
G
H
[code].....
In this case, the "Team 1" and "Team 2" and "Team 3" references what team they are on that month. If it is blank, they aren't with the company any longer. The formula I am using is intended to import this data elsewhere, and is formatted like this:
=IF(ISBLANK(B2), "", A2) - My understanding is that this checks to see if B2 has data, and if it does, it inputs the employee name (A3) in that cell.
My ultimate goal is to be able to change the column referenced after the "isblank" calculation based on the date. So if today is April 2013, I want it to check B2, but if it's December 2013, I want the formula to check J2. Is there a way to do this? I don't mind if it's two steps (like if I have to put the date somewhere in the spreadsheet in order to run the calculation), but ultimately it would be the type of thing I could do that would leverage the existing data set so that I don't have to maintain two different spreadsheets of information.
I have a table of data (total 142 rows). Column contains dates, in the format dd-mmm-yyyy.
I tried to filter using DATE FILTERS->EQUALS and in the custom filter window, I chose EQUALS then picked a date from the date picker icon. The date I picked was 5/4/2009 (this is May 4, 2009, formatted automaticall by excel as m/d/yyyy).
When I clicked OK, nothing showed up despite the fact that there are 6 occurences of May 4, 2009 (formatted as dd-mmm-yyyy in the data table)
So my questions are:
1. Is this due to the formatting?
2. Is there a way to change the date format supplied by the date picker?
=CHOOSE(WEEKDAY("1 Jan " &$C$1),"Sun","Mon","Tue","Wed","Thu","Fri","Sat")
where C1 = the year 2006. B1 has the month Jan and I want to input that into my formula, so when I change the month, it automatically will change my formula. I tried =CHOOSE(WEEKDAY("1 $B$1 " &$C$1),"Sun","Mon","Tue","Wed","Thu","Fri","Sat")
B1 = Jan
C1 = 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 RelatedI want to drag a formula however i want one of the values of the formula to change and the other not to change. for example:
c1=a1+b1
c2=a1+b2
c3= a1+b3
: : :
how can i make it do this when i drag? a1 is not just a constant and it depends on other parameters.
Basically the situation I have is Sheet2 has many references to cells in Sheet1. Sheet2 is for all intents and purposes a kind of nicely formatted report form, and Sheet1 is the input form.
My ultimate goal is to automatically resize row heights on Sheet2 when cell contents change on Sheet2.
Using a worksheet_change event isn't working I presume because it doesn't see the formula output change as a worksheet change, the worksheet_change is firing only when the input is changed in Sheet1.
how can I capture these formula output changes on Sheet2 (triggered from input on Sheet1) OR is there a way of making a particular sheets rows always adjust in height to best fit?
I want to edit a cell's value without changing the formula the cell contains.
View 10 Replies View RelatedI have to excel files
1. Temperature & Humidity Reading
2. Summary
I tried to copy a date from Temperature & Humidity Reading file using the formula ='[Temperature & Humidity Reading.xlsx]Sensor 7'!$C$2 to Summary file
if the date format is like this 6/10/2013 12:00:00 AM the result is fine, but when i try to copy that formula for the succeeding dates the results is same from what i copied.
Let's say I have thousands of employees, but I need to determine who worked for me during a particular date range, and all I have to go on is their start date in one column and their end date in another column.
If:
A1 contains beginning date of employment
B1 contains ending date of employment
C1 contains specified beginning date (criteria)
D1 contains specified ending date (criteria)
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.
This is a project plan with tasks and dates. Column A is the activity number. (Example 1, 2, 3" etc). Column B is the task (Ex. "Complete Report"). Column C is number of days required to complete the task. Column D is the dependency column. (Ex. Cell D2 =1 in other words Task 2 is dependent on task 1). Column E is the date.
I would like to have a seperate start date cell and a go live date cell.
The objective is to enter a start date, and have each column E date increase based on the number of days entered in Column C. If a task is dependent on another and I change the number of days in Column C I need the dependent task to change the same amount of days.
The formula below is to change the fill colour of a date cell prior to the date expiring. The 120 value is number of days
=(E7-today())<=120
The problem i have is it only seems to work if i format the cell and use mmm-yy.
I want to use the same formula for dd-mm-yy..
In a particular month, I have some transactions with a consumed date. If the time falls at 12.00 AM in the end of the month, then the transaction goes into the next month. Below is an example:
3/25/2009 12.00.00 AM
4/1/2009 12.00.00 AM
I want all the transactions that fall on 4/1/2009 due to 12.00.00 AM to convert to 3/31/2009.
why I cant change the date format for some of these rows?
Im trying to modify a excel file sent to me by a client but some of the fields seem to be messed up.
Could somebody explain what i must do to get all the rows in a format
yyyy-mm-dd
i have already tried to format the cells, but only some rows update!!
I would like to be able to input the Day and date in coloumn A and have the rest change accordingly
I wish to have it displayed as follows Monday 03/03/2014 followed by coloumn 2....Tuesday 04/03/2014.
There are 4 cells for each day set that have been "Merged and Centred". (B2,C2,D2,E2) display "Monday 03/03/2014" , F2 is a split , (G2,H2,I2,J2) display "Tuesday 04/03/2014" and so on.
I can make the dates change according to the first input but since i must also display the day as well.
when I do Format Cell, the date formats offered use US formats like 20/02/1999. Why does it do this, and can I fix it? I suspect that this the root of the following problem.
I have a button on a userform to enter today's date into a textbox. It enters it as 05/27/2009. The wrong format. I save the data to the worksheet, then use the data form to go to the next record, then back. The date then appears in the correct format as 27/05/2009.
Another related problem: if I enter a date manually into the textbox on the dataform, such as 2/3/2008, it interprets it as Feb. 3, and when I save it to the worksheet, and then use the form to view it, it comes out as 3/2/2008.
I have a userform which allows data to be entered onto a worksheet. One of the fields (a textbox with the standard calendar control) is the date. When the transaction saves, it correctly saves the date as dd/mm/yyyy using
ActiveCell.Value = TxtDate.Text
I also have a form which allows the user to amend a transaction by loading the data onto the form, amend it and save it back to the worksheet.
The problem is that when it picks up the data and re-saves it, it's resaved in the format mm/dd/yyyy which is a problem because I use the month as one of the categories that users can filter the data by.
I also have the following line immediately after each time the data is saved from the form to the worksheet:
Selection.NumberFormat = "dd/mm/yyyy;@"
In column M, i have a long series of times (times of day) which are currently formatted as "1654", "1021", "1628", etc. In column AI, i need to reformat those dates to look like: "00:00:00.000"
What would the formula to accomplish that look like?
I need the following text in cell B:7 to change depending on the date.
"SAP F&R Poland Milestones for Tuesday the 9th of March 2013"
The thing is i need the date to read the date for the previous day? The reason being is that im reporting on the milestones for the previous day.
I guess im looking for a way to have the "Tuesday", "9th", "March" and "2013" to change automatically?
I'm trying to make a directory whenever the macro runs that includes a timestamp. File names cannot include "/" or special characters, so I'm trying to change the format. However, even though NumberFormat changes it, excel still recognizes the date as having mm/dd/yyyy etc. so the directory cannot be made.
Here is a part of the code:
ActiveCell.FormulaR1C1 = "=NOW()"
Selection.NumberFormat = "yyyymmdd-hhmm"
DateTime = Selection.Value
strFileName = "Data Pull"
MkDir "C:..." & strFileName & DateTime
I have a problem in changing a date automatically in a macro.What I want t to do is the following: On sheet "Stock Count" at cell I1 is a date. I want to open new sheet and copy "Stock Count" to this new sheet then rename the new sheet to the date on "Stock Count" cell 1. I have the following:
Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Stock Count").elect
Range("I1").Select
Selection.Copy
Sheets("Sheet1").Select
Sheets("Sheet1").Name = 14-Apr-14"
Sheets("Stock Count".Select
Range("A1").Select
ActiveCell.Paste
I am importing data (using filestream) from txt files with comma delimited values. When I import dates into General type cells it turns a date of dd/mm/yyyy into mm/dd/yyyy only if the date makes sense.
I would like it to import dd/mm/yyyy as dd/mm/yyyy.
I am also interested to know how Excel/VBA deals with dates coming from various sources, what quirks or mode of thought it follows.
i have date values in say, cells a1 to a4
20080522 21:00:00
20080523 14:45:00
20080523 15:00:00
20080523 15:15:00
i would like cell b to tell me when there is a change of date (not change of time), ie insert a 1 in lets say cell b2. i know it will be a simple if function but i dont know how to only read the first 8 digits
I got a simple spreadsheet that I made for personal use. I want to be able to tell when certain boxes were last modified.
For example..
A1: any number
B1: date that A1 was last modified
I want it so each time I change the value in column A1 it will change the date in B1 to the date it was changed (ie, today).
I also need to tally up all the numbers listed in the A column to give a total of all the numbers in a cell below them.
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?