I have a workbook with three worksheets - 1st is an event register, 2nd is a calender (manually done, with no formulas due to format problems I couldn't solve), 3rd is a summary.
I am trying to work out the following;
The first column of the 1st sheet (event register) requires a date.
The second column requires an 'event', from a drop box or list (of seven possibilities). These are working fine.
Is there a formula I can use theat when a user enters a date, say 1 Jan 08 (or the format is "Thursday, 1 January 2008") AND a certain event, say "CALL" that this will automatically populate the next worksheet (calender) on that corresponding date (1 Jan 08)?
There are seven possibilities for each date (eight including "blank" or ""), which I am sure I will be able to extend to each day of the calender once I work this formula out.
I have already formulated the second worksheet (calender) to count the freqency of events into the third worksheet (summary). Hopefully these wont effect the formula I am looking for.
i have a drop down list in cell b16 it has suppliers names in and it has several which are not all one word ie "monk and crane" the thing is that in cell d29 i have a drop down list that is dependant on whats in cell b16 in cell b16 i am using another data validation list using the code
I have a weird problem with Excel. It recognises all numbers as numbers but excluding the number 1. It is only recognised as text as well as a decimals, for example 3.4. So every time I try and add these values up it completely ignores 1 and decimals.
Work shifts run on a four-week, 28-day cycle always beginning on a Monday.
In 2009, the first such cycle began on Monday 12/1/09.
In my 2003 worksheet, I have the current year in A1. I want the date of the beginning of the first cycle in that year to appear in B1. This should update automatically when the year in A1 is updated.
how I can have excel determine what array or predetermined group a piece of data belongs to as it loops through it.
For example...
Say I import the A column via .csv file, I want to then run a macro that recognises the data and then catergorise it... the desired outcome would be the red text in the C column.
I want to be able to added the variable members to each group as such, I'm presuming this is best by a declaration.
I would like to make the SUMIF function conditional on recognising 1 word among a series of text within a cell. For example, SUM all cells containing word PINK, even if there are other words in the cell also. I cannot get the SUMIF function to pick words out of a cell, it will only SUM up if the whole text matches what is in the cell.
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.
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)
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 was wondering if anyone knows how I could enter a date in one cell, then another date in a second cell and in a third cell have it have it so it minuses the first date from the second and calculates the difference outputted in number of days.
is there a formula that will look at A2 and if the date in that column shows May 5, 2013, then in E2 it enters June 1, 2013? I would like E2 to automatically populate the first of the following month of A2.
What I need to do is that I have electrical tools that get tested on a certain date in Column A. I need Column B to look at column A and determine and display in date format the next quarter when test is due.
Practical example: Extension cord got tested on 04/04/12. The next test needs to be conducted on 01/06/12. If the test is overdue and gets conducted on 02/06/12. The column A will change to 02/06/12 and column B should change to 01/09/12 as a result.
I’m trying to resolve this issue and would appreciate some help.
Scenario I have a tracking sheet that tracks development of work to be completed by individuals. I have a due date column that shows when the work should be completed. Once the work has been completed, the user enters his completion date.
I have a formula that provides an overdue warning (Completion date – today’s date) and some conditional formatting.
Problem When I copy the formula through all the cells in the column I get a number (example 39504) and this changes everyday.
How can I eliminate this being shown as it throws out my average development day calculation?
I have a date in cell A1. I have an operator in cell A2. This info is then merged in to cell A3 with the following formula: =A2&TEXT(A1,"dd/mm/yyyy"). My data range is called "wc_date_of_loss". I want to use this info in a SUMPRODUCT formula (as the dates and operators can be changed by the end user). What's the correct syntax for that?
way to have the vehicle registration date compared to todays date and have it return either due next month, due or over due.
Todays date is in format month/day/year, registration is only by month/year. Todays date is in fixed location in worksheet updated by function Today()
Basically a formula that resembles =If(registration date < todays date, overdue, If( registration date = todays date, due, If(registration date = todays date+1month, next month, If( registration date > todays date + 1 month, 0))))
I am attempting to pick up a date with time entry on a worksheet and place it into a TextBox on a UserForm. Format on the sheet is mm/dd/yyyy h:mm AM/PM. The UserForm is placing the value as mm/dd/yyyy 12:00 AM. here is the
Private Sub UserForm_Initialize() If Not Range("dDate").Value = "" Then TextBox2.Value = Range("dDate").Value TextBox2.Text = Format(DateValue(TextBox2.Text), "mm/dd/yy h:mm AM/PM") Else TextBox2.Value = "" TextBox2.SetFocus End If End Sub
"dDate" is the named range where the date is sitting. The format is also set on the TextBox2 exit event. Can anyone see why only the date portion is being transfered with the default 12:00 AM for no time component of the value?
i use dates a LOT in my job however if i want to add a month to a date the best i came up with is the following:
BTW all dates are USA standard (m/d/y)
A1 = 12/1/2008 Lets say i want to add 3 months but the day NEEDS to be 1 because of all of my array sumif's are based off of month 12/08, 2/09 etc, and in excel 12/08 actually translates to 12/1/08. So here is the formula I have been using for the last 4 years....
=date(year(a1),month(a1)+3,day(1)
that seems a bit "Clunky" to me anyone have a better idea?
do not tell me "=a1+30" or "31" because that does not work universally.
How can I have an if statement or other type of formula look at a date and add 26 months and determine if it's 26 months old yet.
example assuming that November 4, 2005 is one day from being 26 months as of today That cell next to it would say "Available" Then the next it would say "Retired" because it hit the 26 month old period.
I currently use the following formula: =workday(H21,H23,J20:J34) which calculates my date based the date entered in cell H21. While testing I realized that I needed to add another variable to the formula for a specific scenario. When my Date in H21 is less than todays date I require another workday formula to be calculated. How can I add this if statement to the existing formula?
Is there a way to end a formula by using a date? I have a workbook that contains a page for every month I would like for the formulas for march to work until April 1st and then just keep the number value that is there. I don't know if this makes any sense or not but let me know if you need any more details.
I am trying to use an IF formula for finding specific dates, the problem I encounter is that the date in the sheet I am trying to reference is Apr-08. When I do the COUNTIF formula the only way it will work is if I enter
=COUNTIF('Won or Lost Hidden'!$I$4:$I$145,"4/1/2008")
When I try to enter my IF formula
=IF(ROWS($A$3:$A$3)>$A3,"",INDEX('Won or Lost Hidden'!A$4:A$145,SMALL(IF('Won or Lost Hidden'!$I$4:$I$145="4/1/2008",ROW('Won or Lost Hidden'!A$4:A$145)-ROW($C$5)+1),ROWS($A$3:$A3))))
I cannot use the date Apr-08 or 4/1/2008 or any other date for that matter. I have tried using the serial number and so on... I am stumped.
I have attached the workbook so you can see what I am talking about.
I need to have a monthly figure which changes often populate into a cell for each month and then not get overwritten when the sheet is updated .The changing data is being put into cell F3 automatically and I want to put it into different cells monthly so I will have a running record .
I can do something like this in each target cell #=IF(TODAY()=39942,F3,) where "39942" corresponds to a certain date and will be different in each cell but the problem is that when the sheet updates on a new date the previous cells data changes since the statement is no longer true. Is there a way to have the cell only calculate on a certain date and then not change when the date is false ?
Thu1/01/200923Sum of MondaysFri2/01/200923Sat3/01/2009567Sun4/01/200965Mon5/01/20097Tue6/01/200966Wed7/01/20098Thu8/01/200978Fri9/01/20097Sat10/01/200955Sun11/01/2009656Mon12/01/20097Tue13/01/200965Wed14/01/200967Thu15/01/20098Fri16/01/20095Sat17/01/200945Sun18/01/2009987Mon19/01/20095Tue20/01/20096Wed21/01/20097Thu22/01/200968Fri23/01/20096Sat24/01/20098Sun25/01/20096Mon26/01/200947Tue27/01/200967Wed28/01/20094Thu29/01/200976Fri30/01/20096Sat31/01/2009879 This is my spreedsheet. Note. A1=B1 with a custom format of ddd. I would like to have the Sum of Mondays in E1.
I am trying sum if, but can not get it to recognise column A as text.
