dates in formulas
I would like my spreadsheet to add a month to a cell based upon the value of the date in another cell. In other words, I would enter the date in A1 and be formatted as Oct-2009, and I would like to put a formula in A2 that would result in the display of Nov-2009. I've tried =A1+1 but that just won't get it as it still returns Oct -09.
Rusty276
excel
39879 articles. 
2 followers.
4 Replies
635 Views
"Russ" <Rusty@alwaysathome.net> wrote: > I've tried =A1+1 but that just won't get it as it > still returns Oct -09. Because you added one __day__, not one month. Try one of the following: =date(year(A1),1+month(A1),day(A1)) =edate(A1,1) If the latter formula causes a #NAME error, see the Help page for EDATE. I suspect you will see no difference, depending on how you entered the actual date. But in general, the advantage of EDATE, besides being simpler to write, is that if the date in A1 is greater than the 28th in Jan or the 30th of some months, EDATE will ensure that the day of the calculated month is no greater than the end of the month, which is usually what we want. ----- original message ----- "Russ" <Rusty@alwaysathome.net> wrote in message news:ajqa35pi9gsrclds7nkmrr8cjl1elra0ur@4ax.com... >I would like my spreadsheet to add a month to a cell based upon the > value of the date in another cell. In other words, I would enter the > date in A1 and be formatted as Oct-2009, and I would like to put a > formula in A2 that would result in the display of Nov-2009. I've > tried =A1+1 but that just won't get it as it still returns Oct -09. >
joeu2004Thanks. I used the edate after having to install it from disc. On Sun, 14 Jun 2009 14:58:49 -0700, "JoeU2004" <joeu2004@hotmail.com> wrote: >"Russ" <Rusty@alwaysathome.net> wrote: >> I've tried =A1+1 but that just won't get it as it >> still returns Oct -09. > >Because you added one __day__, not one month. Try one of the following: > >=date(year(A1),1+month(A1),day(A1)) > >=edate(A1,1) > >If the latter formula causes a #NAME error, see the Help page for EDATE. > >I suspect you will see no difference, depending on how you entered the >actual date. > >But in general, the advantage of EDATE, besides being simpler to write, is >that if the date in A1 is greater than the 28th in Jan or the 30th of some >months, EDATE will ensure that the day of the calculated month is no greater >than the end of the month, which is usually what we want. > > >----- original message ----- > >"Russ" <Rusty@alwaysathome.net> wrote in message >news:ajqa35pi9gsrclds7nkmrr8cjl1elra0ur@4ax.com... >>I would like my spreadsheet to add a month to a cell based upon the >> value of the date in another cell. In other words, I would enter the >> date in A1 and be formatted as Oct-2009, and I would like to put a >> formula in A2 that would result in the display of Nov-2009. I've >> tried =A1+1 but that just won't get it as it still returns Oct -09. >>
Hi, I prefer EDATE, but here is another solution =A1-DAY(A1)+32 Since you are formatting you dates as MMM-YY this formula should work just fine. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Russ" wrote: > I would like my spreadsheet to add a month to a cell based upon the > value of the date in another cell. In other words, I would enter the > date in A1 and be formatted as Oct-2009, and I would like to put a > formula in A2 that would result in the display of Nov-2009. I've > tried =A1+1 but that just won't get it as it still returns Oct -09. > >
ShaneDevenshireHi Russ, you can use either of the following two formulas in cell A2. =EOMONTH(A1,1) or, =DATE(YEAR(A1),MONTH(A1),DAY(A1)+30) Note:- Do the formatting as you are doing. MMM-YYYY -- Click on Yes, if it is useful. Thanks & Best Regards, Dilip Kumar Pandey MBA, BCA, B.Com(Hons.) dilipandey@yahoo.com dilipandey@gmail.com New Delhi, India "Russ" wrote: > I would like my spreadsheet to add a month to a cell based upon the > value of the date in another cell. In other words, I would enter the > date in A1 and be formatted as Oct-2009, and I would like to put a > formula in A2 that would result in the display of Nov-2009. I've > tried =A1+1 but that just won't get it as it still returns Oct -09. > >
dilipandey1
Similar Artilces:
Find the number of years between two dates
I have aroster of players with thier date of births. I would like to automatically add their ages in another column Try this Formula, assuming that the dob is in cell A1 =DATEDIF(A1,NOW(),"y") & " years, " & DATEDIF(A1,NOW(),"ym") & " months, " & DATEDIF(A1,NOW(),"md") & " days" regards Mukesh "duke" wrote: > I have aroster of players with thier date of births. I would like to > automatically add their ages in another column On Fri, 9 Sep 2005 19:47:02 -0700, "duke" <duke@disc...
In EXCEL, how do I make date-time the x-axis of a plot??????
Under Format/Cells/Date (or Time) I can pick a Type as 3/14/01 1:30 PM. And entering that within a cell works fine. But if I try to use that column as the x-axis of a graph it only supports the date. For example a value for a reading at 3/14/01 1:30 PM and one given for 3/14/01 2:30 PM will appear as two events both given as 3/14/01. The date part is the x-axis, but the time part is simply lost. How can I make both date and time be on the x-axis, so two events at different times on the same day will show up separately? You have to use an XY chart to get thee fractional days (i.e., t...
Changing Cell Reference in Formula
Excel 2007 I've got this big long formula: =IF(AND(CODE(RIGHT(A2,1))>=188,CODE(RIGHT(A2,1))<=190),1*LEFT(A2,LEN(A2)-1) +(CODE(RIGHT(A2,1))-187)/4,A2)-IF(AND(CODE(RIGHT(B2,1))>=188,CODE(RIGHT(B2,1))<=190),1*LEFT(B2,LEN(B2)-1) +(CODE(RIGHT(B2,1))-187)/4,B2) What I want to do is go through and change all of the A2 to k16, and change all of the b2 to k15. What is the easiest way to do that? Select at least two cells (click on this cell, and ctrl-click on an empty cell) Then use a couple of Edit|Replace's. JimS wrote: > > Excel 2007 > > I've got this b...
excel printing problem with my worksheet formula
Hi I am fairly new to excel. I added the automatic list number to worksheet that I am doing. So here is my problem I have. When I use th automatic list numbering formula I set it to count up to like 50 items. Now when I use it for like sales and count tracking sometimes will sell 15 or 50 or even 150 items or more each sale. Now when I wan to print each sale I cant get it to print only the items I want like 1 or 50 depending on the sale. It will print all 5 pages that I set m count up to, which was 500 items. So 15 items is not even one page o but I will get all 5 pages in all. "1 with...
How to fill missing cells in the collumn of dates?
Hi, I have large set of data. I have two columns for every variable: first consists of dates (from 01.01.2000 to 01.01.2008) and the second column consists of values for every date. The problem is that some dates are missed (for example I have 01.01.2000 and next row is 05.01.2000). Is it possble to insert row for every missed date and fill the date in the first column? I can do it manually but I have 7 variables with different set of dates so it will take ages to do it manually. Thanks. In column A I have: A,B,blank,blank,C,blank, blank,blank,D In G1 type =A1 In G2 type =IF(ISBLANK(...
pricelists with formulas + offers
regarding the creation of price lists: In Companies you see very often, that they offer a product (for example software) for a variable price, depending of how many licences they sell. So for example the same product for 5 users it costs X $, for 10 users it costos Y $ and for 13 users Z $. Having this situation imagine that you also have 50 diferent modules of the product (for example an ERP-system). To create a pricelist for that, how would you do that? Is it possible to insert a formula for each module which calculates the price depending of the users? Because if not we should hav...
Check if date is between two dates, then sum only those rows
I have tab1 with begin date in column A, end date in column B. Basically they are weeks, Mon-Sun (e.g. 01/04/2010 and 01/10/2010 in A/B resp.). Tab2 has data I'd like to count and sum. The transaction date is in column B. I want to sum tab2 column G (and a few other columns). Tab2 will be dynamic as I add records to it. Tab1 remains static with each week comprising one row. -- streetcar The best tool for it is a pivot table. Put in your layaut col A & B in row section, col G in Data section (double click to change it to sum - "sumarize by") Click yes...
Auto fill criteria/questions on filling formulas down
I am aware of all the basic features of the fill handle- filling down, auto fill, dragging, absolute/relative references, etc. My question refers to what happens when I double-click on the fill handle. Typically, it fills the formula down until the bottom of my table (this is what I WANT to happen). Every now and then, in different workbooks, I will double-click the fill handle and it will behave differently. Sometimes it will fill a few cells down, sometimes it will do nothing at all. I want it to fill to the bottom of my table every time so that I can make a macro for it (as I have done...
Formula needed 01-22-10
Hi I need a formula that will look in one column range for a certain value and for another value in another column range and when they are both found in the same row, will count the number of occurances. Help!!!! Krissy wrote: > Hi > I need a formula that will look in one column range for a certain value and > for another value in another column range and when they are both found in the > same row, will count the number of occurances. Help!!!! http://www.contextures.com/xlFunctions01.html#SumProduct Omit the "values to be summed". =SUMPRODUCT(--(A2...
Numeric content in one cell ( implicit formula ) and the result in another one
Hi, I made some search before, but too much information at the same time. So I post this question: In cell D2 , I have the following content : 10002/(14971213 - 37375) /1000000) In cell E2, I would like to have the result value of data in cell D2 : 669,754 I would not work with "left...len... search... right.." as the format ( then formula ) in cell D2 may change. Is there a function giving the computed result of a cell and put the result in another one? Best regards Pierre In cell E2, will the formula "=D2" do it for you? Or is that too simplistic and I don'...
formula slowing up processing
I have the following formula in a list that takes up to 15 seconds to calculate. When I am inputting new entries into the list everything freezes. I know its this formula because when I remove it everything runs smoothly. The formula is used to lookup a number on another list and then sum all the values returned for that number. Can anyone see a problem with the formula...it does work...just excruciatingly slowly: SUMIF('Blending Details'!$A:$A$,A2,'Blending Details'!$E:$E) Have you tried reducing the size of the range? Do you really need 65,536 rows? -- Regards, RD --...
How do I auto calculate 90 days days from a certain date?
In a law office, we have to calculate from a date of filing either 30, 60 or 90 days from a certain date to calendar another due date item. Is there a way for Outlook to auto calculate 90 days from a date entered? If you're in the Calendar folder hit Control-G (for Go to) and enter the date followed by (without quotes) "+90d". You should end up on the day in question "T Cummings" <T Cummings@discussions.microsoft.com> wrote in message news:BF3927FC-7E90-433D-99DF-C9B32C5F384E@microsoft.com... > In a law office, we have to calculate from a date of filing...
Creating a formula that uses multiple logics.
I need to create a formula that pulls data from multiple cells. The formula is needed to return a set amount of money after certain criteria are met. I.E. (If associates rating is 1 [cell reference 1] and years of service is greater than ten [cell reference 2] and they have been in job more than 3 years [cell reference 3] then return "$.20". There will also be needed a reference to a 2 rating and a different dollar amount. Also multiple year intervals 10-15 years 1 rating = .20 2 rating = .15 15-20 years 1 rating = .25 2 rating = .20 20+ years 1 rating = .30 2 r...
plz help in to creat formula of excel
hello to all i want to discuss my problem with you related to MS excel. i want to know or find out formula of excel which can help me in such way.... i want to make grade point of my college students in such a way.. for example.... marks grade point average 50 1 53 1.3 60 2 68 2.8 70 3 76 3.6 80 4 84 4 89 4 90 4 98 4 100 4 this is example the student who get 50 marks the GPA will be 1 and who get 80 marks or above 80 the GPA should be 4 GPA should not less than 1 or grator than 4 please tell me the formula so that i can make this GPA thanks a lo -- khushe --------------------------...
Overwriting a cell with a formula without deleting the formula
Hello. I am creating an Expense Report worksheet and have created a simple formula that will calculate mileage based on total miles. Below is my worksheet data. A B C D 1 Expense Type Acct. Code Total Miles Amount 2 Airfare 11111 $250.00 3 Mileage 22222 20 $10.00 I am trying to figure out a way to create a conditional formula so that IF Expense...
How can I protect certain columns on a sheet so that formulas are.
How do I go about protecting certain columns on an excel sheet so that formulas are not changed by someone else entering data onto the worksheet Right click on the cells you want to allow data entry in. Then select "format cells", Then "protection". uncheck the "locked" box. Then protect the worksheet. "Tan" <Tan@discussions.microsoft.com> wrote in message news:B7E54609-EA18-4882-8106-7B954F0C3F8B@microsoft.com... > How do I go about protecting certain columns on an excel sheet so that > formulas are not changed by someone else entering...
Excel
Can anyone tell me how to convert a date to the number of days ago that date actually was? I need to find the number of days ago. Thanks in advance! Michael the function Today() returns you a number when the cell is formatted 'General' or 'Number' -today sept 29 you get 37893-. Apply the same to your date and deduct one from the other Generally speaking, why don't you read 'About dates and date systems' in Excel Help Emm >-----Original Message----- >Can anyone tell me how to convert a date to the number of >days ago that date actually was? I need to...
Record AFTER latest by date an time, grouped by serial number
I have 2 tables: tblEqLoc has the following fields: Start Date | StartTime | EndDate | EndTime | CustNum | SerialNum | EqStatus tblEndDate has : EndDate, CustNum, and SerialNum that can be joined with tblEqLoc In the CustNum field is either a customer number or a warehouseID number. CustNums always start with 3004 – all other numbers start with zeros. When all the data is sorted ascending chronologically, the start date and start time for any given record is always equal to the end date / end time for the previous record. I need to create a query that will show the ...
Newbie
I have a spreadsheet that is little more than a list of PO #'s that shows what orders are done and ready to ship for a particular customer. We upload the file every day to a website where the customer can view it whenever they want. So far, everything's fine. I use the "=TODAY()" function so that when I open and modify the listings, the date gets automatically updated. The problem is, when the customer accesses the file, the date also updates on THEIR end and the file looks more current than it really is. Is there a way to maybe lock in the date ONLY when I save the file...
How do I import fractions without data being converted to dates?
I am importing data that was created in France and they used fractions for the inch data rather than decimals. Now, when I try to import that data, even though the preview shows it importing correctly, it converts the fractions to dates because I suppose it sees the forward slash. How do I make it recognize them as fractions and not dates? Thanks, Ray Proeber rproeber@execpc.com Ray, Format the column for text in the import wizard. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "rproeber" <rproeber@discussions.mi...
Date Calculation #3
I have a spread sheet with dates entered in the range K2:K750 and in O2:O750. There are many dates that have not yet been entered. I am calculating the number of weeks between the two dates with the formula =(O2-K2)/7 and then filled the formula down to row 750. This is done in column P. My problem is that I must calculate the average number of weeks. How do I create a formula to calculate the average of values in the range P2:P750, without including those cells in rows where one or both of the dates in column K or O have not yet been entered. The formula should update as date informatio...
How do I set up a sheet with date headers one week apart.
I am trying to set up a spreadsheet with date headers and/or side labels, one week apart. I have tried without success. Any help gratefully received. 1/1/2006 - 8/1/2006 - 15/1/2006 etc. Frank Taylor Dell Dimension 8400 4 Gig RAM 1.3 GigHz Pentium CPU Multithreaded Windows XP Pro - SR2 Canon S530D 160 Gig Hard Drive. Hi Type your first date in A2. In the next cell use = A2+7 You can fill this across as necessary. Andy. "mossbury" <mossbury@discussions.microsoft.com> wrote in message news:B8E4DAB1-C24F-4AE1-8BDA-2E854FEE78C8@microsoft.com... >I am trying to set up...
Is there a function/formula for this?
I have a long column of numbers. I would like to know which of these numbers will sum to a target number. I don't know how many of the numbers are in the sum. Here is an example to better illustrate my question: Column A 12 73.29 45.33 94.31 34.22 18 23.44 I want to know what combination of these numbers will give me the sum of the target number 148.62. Is there a formula or function that can do this? Hi do you need only one combination or all possible combinations. The altter one is for a 'long' column not feasible -- Regards Frank Kabel Frankfurt, Germany "outlet321&q...
How to records that the date field = todays date in VBA
DATE CODE DESCRIPTION OLD NEW UOM ------------- ------- ---------------------------------------- ----- ------ ------ 3/26/2010 *1438 ETHYL-2-METHYL BUTYRATE(E) 8.14 9.15 KG 4/6/2010 *0068 BENZYL BENZOATE(E)(I43) 5.00 6.00 KG How do I program in VBA to set the print area that the date field is today's date? Thanks. PC Did you only want todays date or did you want everything up to todays date? What if there is more than one instance of todays date? What if they are not con...
Start Date for Benefits
Can we enter Benefits for a new employee that don't actually take effect till 10-01 while still enabling them as active? Anyone or was i not clear in my question? On Aug 17, 1:11=A0pm, BAM <minchs...@gmail.com> wrote: > Can we enter Benefits for a new employee that don't actually take > effect till 10-01 while still enabling them as active? ...
I have aroster of players with thier date of births. I would like to automatically add their ages in another column Try this Formula, assuming that the dob is in cell A1 =DATEDIF(A1,NOW(),"y") & " years, " & DATEDIF(A1,NOW(),"ym") & " months, " & DATEDIF(A1,NOW(),"md") & " days" regards Mukesh "duke" wrote: > I have aroster of players with thier date of births. I would like to > automatically add their ages in another column On Fri, 9 Sep 2005 19:47:02 -0700, "duke" <duke@disc...
In EXCEL, how do I make date-time the x-axis of a plot??????
Under Format/Cells/Date (or Time) I can pick a Type as 3/14/01 1:30 PM. And entering that within a cell works fine. But if I try to use that column as the x-axis of a graph it only supports the date. For example a value for a reading at 3/14/01 1:30 PM and one given for 3/14/01 2:30 PM will appear as two events both given as 3/14/01. The date part is the x-axis, but the time part is simply lost. How can I make both date and time be on the x-axis, so two events at different times on the same day will show up separately? You have to use an XY chart to get thee fractional days (i.e., t...
Changing Cell Reference in Formula
Excel 2007 I've got this big long formula: =IF(AND(CODE(RIGHT(A2,1))>=188,CODE(RIGHT(A2,1))<=190),1*LEFT(A2,LEN(A2)-1) +(CODE(RIGHT(A2,1))-187)/4,A2)-IF(AND(CODE(RIGHT(B2,1))>=188,CODE(RIGHT(B2,1))<=190),1*LEFT(B2,LEN(B2)-1) +(CODE(RIGHT(B2,1))-187)/4,B2) What I want to do is go through and change all of the A2 to k16, and change all of the b2 to k15. What is the easiest way to do that? Select at least two cells (click on this cell, and ctrl-click on an empty cell) Then use a couple of Edit|Replace's. JimS wrote: > > Excel 2007 > > I've got this b...
excel printing problem with my worksheet formula
Hi I am fairly new to excel. I added the automatic list number to worksheet that I am doing. So here is my problem I have. When I use th automatic list numbering formula I set it to count up to like 50 items. Now when I use it for like sales and count tracking sometimes will sell 15 or 50 or even 150 items or more each sale. Now when I wan to print each sale I cant get it to print only the items I want like 1 or 50 depending on the sale. It will print all 5 pages that I set m count up to, which was 500 items. So 15 items is not even one page o but I will get all 5 pages in all. "1 with...
How to fill missing cells in the collumn of dates?
Hi, I have large set of data. I have two columns for every variable: first consists of dates (from 01.01.2000 to 01.01.2008) and the second column consists of values for every date. The problem is that some dates are missed (for example I have 01.01.2000 and next row is 05.01.2000). Is it possble to insert row for every missed date and fill the date in the first column? I can do it manually but I have 7 variables with different set of dates so it will take ages to do it manually. Thanks. In column A I have: A,B,blank,blank,C,blank, blank,blank,D In G1 type =A1 In G2 type =IF(ISBLANK(...
pricelists with formulas + offers
regarding the creation of price lists: In Companies you see very often, that they offer a product (for example software) for a variable price, depending of how many licences they sell. So for example the same product for 5 users it costs X $, for 10 users it costos Y $ and for 13 users Z $. Having this situation imagine that you also have 50 diferent modules of the product (for example an ERP-system). To create a pricelist for that, how would you do that? Is it possible to insert a formula for each module which calculates the price depending of the users? Because if not we should hav...
Check if date is between two dates, then sum only those rows
I have tab1 with begin date in column A, end date in column B. Basically they are weeks, Mon-Sun (e.g. 01/04/2010 and 01/10/2010 in A/B resp.). Tab2 has data I'd like to count and sum. The transaction date is in column B. I want to sum tab2 column G (and a few other columns). Tab2 will be dynamic as I add records to it. Tab1 remains static with each week comprising one row. -- streetcar The best tool for it is a pivot table. Put in your layaut col A & B in row section, col G in Data section (double click to change it to sum - "sumarize by") Click yes...
Auto fill criteria/questions on filling formulas down
I am aware of all the basic features of the fill handle- filling down, auto fill, dragging, absolute/relative references, etc. My question refers to what happens when I double-click on the fill handle. Typically, it fills the formula down until the bottom of my table (this is what I WANT to happen). Every now and then, in different workbooks, I will double-click the fill handle and it will behave differently. Sometimes it will fill a few cells down, sometimes it will do nothing at all. I want it to fill to the bottom of my table every time so that I can make a macro for it (as I have done...
Formula needed 01-22-10
Hi I need a formula that will look in one column range for a certain value and for another value in another column range and when they are both found in the same row, will count the number of occurances. Help!!!! Krissy wrote: > Hi > I need a formula that will look in one column range for a certain value and > for another value in another column range and when they are both found in the > same row, will count the number of occurances. Help!!!! http://www.contextures.com/xlFunctions01.html#SumProduct Omit the "values to be summed". =SUMPRODUCT(--(A2...
Numeric content in one cell ( implicit formula ) and the result in another one
Hi, I made some search before, but too much information at the same time. So I post this question: In cell D2 , I have the following content : 10002/(14971213 - 37375) /1000000) In cell E2, I would like to have the result value of data in cell D2 : 669,754 I would not work with "left...len... search... right.." as the format ( then formula ) in cell D2 may change. Is there a function giving the computed result of a cell and put the result in another one? Best regards Pierre In cell E2, will the formula "=D2" do it for you? Or is that too simplistic and I don'...
formula slowing up processing
I have the following formula in a list that takes up to 15 seconds to calculate. When I am inputting new entries into the list everything freezes. I know its this formula because when I remove it everything runs smoothly. The formula is used to lookup a number on another list and then sum all the values returned for that number. Can anyone see a problem with the formula...it does work...just excruciatingly slowly: SUMIF('Blending Details'!$A:$A$,A2,'Blending Details'!$E:$E) Have you tried reducing the size of the range? Do you really need 65,536 rows? -- Regards, RD --...
How do I auto calculate 90 days days from a certain date?
In a law office, we have to calculate from a date of filing either 30, 60 or 90 days from a certain date to calendar another due date item. Is there a way for Outlook to auto calculate 90 days from a date entered? If you're in the Calendar folder hit Control-G (for Go to) and enter the date followed by (without quotes) "+90d". You should end up on the day in question "T Cummings" <T Cummings@discussions.microsoft.com> wrote in message news:BF3927FC-7E90-433D-99DF-C9B32C5F384E@microsoft.com... > In a law office, we have to calculate from a date of filing...
Creating a formula that uses multiple logics.
I need to create a formula that pulls data from multiple cells. The formula is needed to return a set amount of money after certain criteria are met. I.E. (If associates rating is 1 [cell reference 1] and years of service is greater than ten [cell reference 2] and they have been in job more than 3 years [cell reference 3] then return "$.20". There will also be needed a reference to a 2 rating and a different dollar amount. Also multiple year intervals 10-15 years 1 rating = .20 2 rating = .15 15-20 years 1 rating = .25 2 rating = .20 20+ years 1 rating = .30 2 r...
plz help in to creat formula of excel
hello to all i want to discuss my problem with you related to MS excel. i want to know or find out formula of excel which can help me in such way.... i want to make grade point of my college students in such a way.. for example.... marks grade point average 50 1 53 1.3 60 2 68 2.8 70 3 76 3.6 80 4 84 4 89 4 90 4 98 4 100 4 this is example the student who get 50 marks the GPA will be 1 and who get 80 marks or above 80 the GPA should be 4 GPA should not less than 1 or grator than 4 please tell me the formula so that i can make this GPA thanks a lo -- khushe --------------------------...
Overwriting a cell with a formula without deleting the formula
Hello. I am creating an Expense Report worksheet and have created a simple formula that will calculate mileage based on total miles. Below is my worksheet data. A B C D 1 Expense Type Acct. Code Total Miles Amount 2 Airfare 11111 $250.00 3 Mileage 22222 20 $10.00 I am trying to figure out a way to create a conditional formula so that IF Expense...
How can I protect certain columns on a sheet so that formulas are.
How do I go about protecting certain columns on an excel sheet so that formulas are not changed by someone else entering data onto the worksheet Right click on the cells you want to allow data entry in. Then select "format cells", Then "protection". uncheck the "locked" box. Then protect the worksheet. "Tan" <Tan@discussions.microsoft.com> wrote in message news:B7E54609-EA18-4882-8106-7B954F0C3F8B@microsoft.com... > How do I go about protecting certain columns on an excel sheet so that > formulas are not changed by someone else entering...
Excel
Can anyone tell me how to convert a date to the number of days ago that date actually was? I need to find the number of days ago. Thanks in advance! Michael the function Today() returns you a number when the cell is formatted 'General' or 'Number' -today sept 29 you get 37893-. Apply the same to your date and deduct one from the other Generally speaking, why don't you read 'About dates and date systems' in Excel Help Emm >-----Original Message----- >Can anyone tell me how to convert a date to the number of >days ago that date actually was? I need to...
Record AFTER latest by date an time, grouped by serial number
I have 2 tables: tblEqLoc has the following fields: Start Date | StartTime | EndDate | EndTime | CustNum | SerialNum | EqStatus tblEndDate has : EndDate, CustNum, and SerialNum that can be joined with tblEqLoc In the CustNum field is either a customer number or a warehouseID number. CustNums always start with 3004 – all other numbers start with zeros. When all the data is sorted ascending chronologically, the start date and start time for any given record is always equal to the end date / end time for the previous record. I need to create a query that will show the ...
Newbie
I have a spreadsheet that is little more than a list of PO #'s that shows what orders are done and ready to ship for a particular customer. We upload the file every day to a website where the customer can view it whenever they want. So far, everything's fine. I use the "=TODAY()" function so that when I open and modify the listings, the date gets automatically updated. The problem is, when the customer accesses the file, the date also updates on THEIR end and the file looks more current than it really is. Is there a way to maybe lock in the date ONLY when I save the file...
How do I import fractions without data being converted to dates?
I am importing data that was created in France and they used fractions for the inch data rather than decimals. Now, when I try to import that data, even though the preview shows it importing correctly, it converts the fractions to dates because I suppose it sees the forward slash. How do I make it recognize them as fractions and not dates? Thanks, Ray Proeber rproeber@execpc.com Ray, Format the column for text in the import wizard. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "rproeber" <rproeber@discussions.mi...
Date Calculation #3
I have a spread sheet with dates entered in the range K2:K750 and in O2:O750. There are many dates that have not yet been entered. I am calculating the number of weeks between the two dates with the formula =(O2-K2)/7 and then filled the formula down to row 750. This is done in column P. My problem is that I must calculate the average number of weeks. How do I create a formula to calculate the average of values in the range P2:P750, without including those cells in rows where one or both of the dates in column K or O have not yet been entered. The formula should update as date informatio...
How do I set up a sheet with date headers one week apart.
I am trying to set up a spreadsheet with date headers and/or side labels, one week apart. I have tried without success. Any help gratefully received. 1/1/2006 - 8/1/2006 - 15/1/2006 etc. Frank Taylor Dell Dimension 8400 4 Gig RAM 1.3 GigHz Pentium CPU Multithreaded Windows XP Pro - SR2 Canon S530D 160 Gig Hard Drive. Hi Type your first date in A2. In the next cell use = A2+7 You can fill this across as necessary. Andy. "mossbury" <mossbury@discussions.microsoft.com> wrote in message news:B8E4DAB1-C24F-4AE1-8BDA-2E854FEE78C8@microsoft.com... >I am trying to set up...
Is there a function/formula for this?
I have a long column of numbers. I would like to know which of these numbers will sum to a target number. I don't know how many of the numbers are in the sum. Here is an example to better illustrate my question: Column A 12 73.29 45.33 94.31 34.22 18 23.44 I want to know what combination of these numbers will give me the sum of the target number 148.62. Is there a formula or function that can do this? Hi do you need only one combination or all possible combinations. The altter one is for a 'long' column not feasible -- Regards Frank Kabel Frankfurt, Germany "outlet321&q...
How to records that the date field = todays date in VBA
DATE CODE DESCRIPTION OLD NEW UOM ------------- ------- ---------------------------------------- ----- ------ ------ 3/26/2010 *1438 ETHYL-2-METHYL BUTYRATE(E) 8.14 9.15 KG 4/6/2010 *0068 BENZYL BENZOATE(E)(I43) 5.00 6.00 KG How do I program in VBA to set the print area that the date field is today's date? Thanks. PC Did you only want todays date or did you want everything up to todays date? What if there is more than one instance of todays date? What if they are not con...
Start Date for Benefits
Can we enter Benefits for a new employee that don't actually take effect till 10-01 while still enabling them as active? Anyone or was i not clear in my question? On Aug 17, 1:11=A0pm, BAM <minchs...@gmail.com> wrote: > Can we enter Benefits for a new employee that don't actually take > effect till 10-01 while still enabling them as active? ...