Modifying date formulas
Hi, I have built a spread sheet and one of the things I'm measuring is targets for completion of an event, the target is measured in weeks from an entered start date to an entered completion date, I have used the following formula, based on K2 as the start date and Z2 as the completion date: =DATEDIF($K2,$Z2,"d")/7 This works fine however now I want to modify the formula to say that if the start date entered is equal to or before (<=) 31/04/2009 (39934) then use 31/04/2009 (39934) as the start date if its greater than (>) that then use the date as entered in K2, which gives: =IF($K2<=39934, 39934, $K2) So the question is how do I combine them? -- Steve
stevebakerj
excel.newusers
15348 articles. 
2 followers.
5 Replies
724 Views
On Sun, 19 Jul 2009 11:45:33 +0100, Steve <stevebakerj@bigfoot.com> wrote: >Hi, > >I have built a spread sheet and one of the things I'm measuring is >targets for completion of an event, the target is measured in weeks from >an entered start date to an entered completion date, I have used the >following formula, based on K2 as the start date and Z2 as the >completion date: > >=DATEDIF($K2,$Z2,"d")/7 > >This works fine however now I want to modify the formula to say that if >the start date entered is equal to or before (<=) 31/04/2009 (39934) >then use 31/04/2009 (39934) as the start date if its greater than (>) >that then use the date as entered in K2, which gives: > >=IF($K2<=39934, 39934, $K2) > >So the question is how do I combine them? > > -- >Steve Comments. Excel stores dates as integers with 1= 1 Jan 1900 (or 2 Jan 1904) 1. Your original formula can be simplified: =(EndDate-StartDate)/7 2. To set a particular StartDate as being the earliest: =(EndDate-MAX(EarliestStartDate,StartDate))/7 --ron
ronrosenfeld"Steve" <stevebakerj@bigfoot.com> wrote:
> =DATEDIF($K2,$Z2,"d")/7
>
> This works fine however now I want to modify the
> formula to say that if the start date entered is
> equal to or before (<=) 31/04/2009 (39934) then
> use 31/04/2009 (39934) as the start date if its
> greater than (>) that then use the date as entered
> in K2
First, no need to use DATEDIF to compute difference in days. Second, no
need to refer to dates by serial number; in fact, I would say it is a bad
idea (impossible to relate to).
Try:
=$Z2 - max($K2, date(2009,4,31))
Alternatively, you can write either of the following. But I think they are
deprecated because they depend on your computer's regional settings.
=$Z2 - max($K2, datevalue("31/04/2009"))
=$Z2 - max($K2, --"31/04/2009")
----- original message -----
"Steve" <stevebakerj@bigfoot.com> wrote in message
news:lO2dnTaL1-fSZP_XnZ2dnUVZ8tadnZ2d@bt.com...
> Hi,
>
> I have built a spread sheet and one of the things I'm measuring is targets
> for completion of an event, the target is measured in weeks from an
> entered start date to an entered completion date, I have used the
> following formula, based on K2 as the start date and Z2 as the completion
> date:
>
> =DATEDIF($K2,$Z2,"d")/7
>
> This works fine however now I want to modify the formula to say that if
> the start date entered is equal to or before (<=) 31/04/2009 (39934) then
> use 31/04/2009 (39934) as the start date if its greater than (>) that
> then use the date as entered in K2, which gives:
>
> =IF($K2<=39934, 39934, $K2)
>
> So the question is how do I combine them?
>
> --
> Steve
joeu2004Errata....
I forgot to divide by 7 to compute weeks. That should be obvious. But....
=($Z2 - max($K2, date(2009,4,31))) / 7
----- original message -----
"JoeU2004" <joeu2004@hotmail.com> wrote in message
news:uUZEbLKCKHA.1248@TK2MSFTNGP04.phx.gbl...
> "Steve" <stevebakerj@bigfoot.com> wrote:
>> =DATEDIF($K2,$Z2,"d")/7
>>
>> This works fine however now I want to modify the
>> formula to say that if the start date entered is
>> equal to or before (<=) 31/04/2009 (39934) then
>> use 31/04/2009 (39934) as the start date if its
>> greater than (>) that then use the date as entered
>> in K2
>
> First, no need to use DATEDIF to compute difference in days. Second, no
> need to refer to dates by serial number; in fact, I would say it is a bad
> idea (impossible to relate to).
>
> Try:
>
> =$Z2 - max($K2, date(2009,4,31))
>
> Alternatively, you can write either of the following. But I think they
> are deprecated because they depend on your computer's regional settings.
>
> =$Z2 - max($K2, datevalue("31/04/2009"))
>
> =$Z2 - max($K2, --"31/04/2009")
>
>
> ----- original message -----
>
> "Steve" <stevebakerj@bigfoot.com> wrote in message
> news:lO2dnTaL1-fSZP_XnZ2dnUVZ8tadnZ2d@bt.com...
>> Hi,
>>
>> I have built a spread sheet and one of the things I'm measuring is
>> targets for completion of an event, the target is measured in weeks from
>> an entered start date to an entered completion date, I have used the
>> following formula, based on K2 as the start date and Z2 as the completion
>> date:
>>
>> =DATEDIF($K2,$Z2,"d")/7
>>
>> This works fine however now I want to modify the formula to say that if
>> the start date entered is equal to or before (<=) 31/04/2009 (39934) then
>> use 31/04/2009 (39934) as the start date if its greater than (>) that
>> then use the date as entered in K2, which gives:
>>
>> =IF($K2<=39934, 39934, $K2)
>>
>> So the question is how do I combine them?
>>
>> --
>> Steve
>
Thank you both, all sorted and lessons learned. -- Steve Beware of all enterprises that require new clothes. - Henry David Thoreau
On Mon, 20 Jul 2009 07:55:58 +0100, Steve <stevebakerj@bigfoot.com> wrote: > >Thank you both, all sorted and lessons learned. Glad to help. Thanks for the feedback. --ron
Similar Artilces:
Autofill dates in a 3D range
I have a workbook with 53 pages (onepage for each week of the year), I want to autofill the date of each weekday across all 53 sheets, You you talking about 53 worksheets, each with a tab? If so, Ctrl+Click the right and left tabs to select them and every tab between. This will Group the worksheets. Any data entered will now be entered in all. When finished, click any tab to ungroup (or right-click and choose Ungroup). -- Greeting from the Gulf Coast! http://myweb.cableone.net/twodays "Gibbo" <anonymous@discussions.microsoft.com> wrote in message news:D7F9351D-8DCC-4333-812...
Problem to modify the size of PlotArea
Hello, I am trying to modify a chart so that it prints in a 20cm x 17,5cm frame. I do understand that this is governed by the plotarea.insideheight and plotarea.insidewidth properties. I also do understant that these properties are read-only. So far my code is the following: With ActiveChart .SizeWithWindow = False .PageSetup.ChartSize = xlScreenSize .PlotArea.Width = 567 .PlotArea.Height = 496 Do While .PlotArea.InsideWidth < 567 .PlotArea.Width = .Pl...
EXE change in data modified
Hi Guys Have a question regarding GP. What can cause a change in the "modified date" to the dynamics.exe file? In theory, something would need to change the file. My best guess would be a service pack would change the date. -- www.fmtconsultants.com "cruesta@gmail.com" wrote: > Hi Guys > > Have a question regarding GP. What can cause a change in the "modified > date" to the dynamics.exe file? > > For GP 9.0 SP 1 the Dynamics.exe modified date is 5/16/2006 which looks to be the date the Service Pack was created (as opposed to the da...
absolute formula in Excel
Hello all we need to use absolute formula in most cells, the way that we know is that use the normal formula and then add $ sign before cell ref. It is a nightmare to correct all cells manually. is there any way to make it fix? Thanks The reason for absolute and relative formulas and bits of formulas is that when you copy them they adjust as you want them to. So at the moment if your cells are giving the correct answer you need to do nothing, if you wish to copy them then you only need to adjust one cell accordingly for each formula type and copy it. Or am I missing something? Regards ...
Modifier Error
Whenever we try to go to modifier it gets the following error. The Modifier is currently unavailable because another user is editing resources in the Forms Dictionary. The Server has already been rebooted but we are still coming up with the same error. Thanks, Hi make sure that you are not sharing the Forms Dictionary with another users. Thanks BS "Jess M." <JessM@discussions.microsoft.com> wrote in message news:C56C6878-9410-4CF5-95DE-DCA0AC50EA55@microsoft.com... > Whenever we try to go to modifier it gets the following error. > > The Modifier is current...
modified timeline graph
I have a table of dates and events (and "phase"), and I want to make a timeline graph: 1-d horizontal line, no (visible) y-axis. I want the distance between the events to be spaced based on the date, and the event names to be shown at each point. The dates should be shown too, either for each event or just based on auto-scaling of the x-axis (e.g. major unit = 3 months). Here's the hard part. The events happen in different "phases", and I want to show the phases graphically somehow. I was thinking this component of the graph would be a bar chart, and I could someho...
How do I get a chart to only display the dates in my table?
I am trying to graph a large group of data. It is a series of dates (not all consecutive) - with the time it takes to do a task on each of the dates. When I graph it - it fills in all the "missing" dates. For instance lets say a set of data would be: Date: 12-1-08 12-5-08 12-6-08 12-6-08 12-12-08 Time: .5 .34 .93 1.2 .25 On my graph it would put a date for every date between 12-1 and 12-12 even if there is no data. Is there any way to have Excel graph only the dates that have data? Change the axis from t...
Formula #6
This is a multi-part message in MIME format. ------=_NextPart_000_0067_01C3D629.9FFCBD90 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello All, I need a formula to transfer a figure from another sheet where I = am looking for the last total of the column higher than zero.. The = formula that is used in the column will not allow me to total at the = bottom of the page thus eliminating the easy transfer of columns. Any = help would be greatly appreciated. Tony ------=_NextPart_000_0067_01C3D629.9FFCBD90 Content-Type: text/htm...
COUNTING DATES #4
How do i write a formula to count the number of lines on a database of information that have a date that matches todays date? Say I have a huge database with 10,000 lines of info and I want it to look at all the info and put a number in a cell that says how many of the lines in the datbase contain todays date. -- na See COUNTIF Worksheet Function in help --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Cody" &l...
Modify name displayed in GAL
Hi all! Is there any way to change the manner in wich user are displayed in GAL? I've been aked to set DislayName to "First Last", but leave "Last First" in GAL - is there any way to do this? Thanx -- R.V. ...
modify online services
My bank recently upgraded their online services to include Direct Statements. Previously I had set up to receive Web statements. Money 2004 Omline services Manager does not give me the option to "modify services" only to "change financial institutions." Any ideas on how I can modify online banking services so I can use background banking to get Direct statements? This is how it works in M03: From account list -> Click manage online services -> click Setup online services, then select the financial institution from the list (assuming the list is updated wi...
Formulas in Comments or Mouseover?
I'm trying to simply my spreadsheet. My goal is to either enter formulas into a comment box or some sort o mouse over function. That way, my sheet isn't crammed with data. Tha data will only appear if I put my mouse over a particular cell. Any help will be greatly appreciated -- pikapika1 ----------------------------------------------------------------------- pikapika13's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1089 View this thread: http://www.excelforum.com/showthread.php?threadid=27681 Hi AFAIK this is not possible in Excel -- Regards Fran...
if blank formula
is there a formula that will do this? formating conditioning / formula is =ifblank(c60),omitpayment:") that is what I have but it is not working. what am I doing wrong? @@ I want, if the cell is blank to show Payment Omitted @@ I'm not completely clear on your question, but I think this is what you're looking for. =IF(ISBLANK(C60),"Payment Omitted","") that formula will test to see if c60 is blank, and if it is, will put "payment omitted." if it is not blank it will return nothing. "Colin2u" wrote: > is there a formula that will ...
Simple Date Range Help
I have a project for work that need data broken up as follows Billed Sales Affiliate International Billed Sales Affiliate Domestic We have special codes for each International and Affiliate, so sorting that will be no problem. I need to know a way to pull the data by just inputting a weekly date range and displaying the total for the week. My supervisor suggested going through the entire backup of excel files for the weeks in the month pulling out everything but what I need then making a pivot table. This seems like a huge process. I know there can be a date range...
Pasting Formulas Without Links
Hi all, Is there a means of pasting formulas from one workbook to another without the formulas linking back to the original workbook? Example: When I do Paste Special>Formulas, I get: ='[Monthly Status Report Template v.2.0.xls]Summary'!$B$2 When what I want is: =Summary'!$B$2 I'm going to have to do this to around 50 spreadsheets, so if I can find an quick way of doing it, it's going to be really useful. Note: there are a number of formulas per worksheet, otherwise I'd just delete it it manually! TIA, SamuelT -- SamuelT ---------------------------------...
GP7.5SP6
We're in the process of upgrading from GP7.5 SP4 to SP6. We are on MSSQL Server 7.0. My supervisor has already implemented the hot fix as describe in MBS (that's a script to update some indexes on the server if someone is using GP7.5 manufacturing on MSSQL7). When I try to import modified forms and reports I've received some errors. For instances some references on the VBA to the GUI forms (i.e. text boxes, buttons) become missing. And on the reports I've been told it couldn't find the table MOP_MO_Variance_DC_Detail_Report_Temp and MOP-Sched_Interface_Report_Before....
Search / Modify / Delete . . .
In �sheet1� I have a database with theses headers �Name, Phone, Address and below are the records rows 2-150. In �sheet2� I have the data entr� form (not VBA) its on the sheet, that has a macro that writes t �Sheet1� (for storage). How can implement a search for a record and return the result t �sheet2�, with the ability to modify or delete from the databas (�sheet1�). Thanking you in advance -- Fabl ----------------------------------------------------------------------- Fable's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=218 View this thread: http://www.ex...
Count cells in a column that contain dates
Column J contains dates in some cells, and text in others. I need to count the number of cells in this one column that contains dates. How can I do this? If the only cells that are in that column that are numeric (like dates), you can use: =count(a:a) (Dates are just numbers formatted nicely in excel.) Cachod1 wrote: > > Column J contains dates in some cells, and text in others. I need to count > the number of cells in this one column that contains dates. How can I do > this? -- Dave Peterson ...
Help with the IF formula
i have created a payroll spreadsheet and now im currently having issues with adding the tax. The tax goes like this: if $346 - $480 deduct $63 and 25c for each dollar over $346 $481 - $672 deduct 96 and 40c for each dollar over $481 i just need help in how i'd go about making a formula like that. there is alot more like that but i'd be able to figure it out i just need to know how i would go creating such a formula? http://www.mcgimpsey.com/excel/variablerate.html -- Regards, Peo Sjoblom (No private emails please, for everyone's benefit keep the discussion in the newsgroup...
Date format #33
I want to have a column to be my date field. I want to be able to enter for instance 09072005 or 90705 I would like for Excel to be able to convert it to read 09/07/2005. If I format my cell to Date it reads as ########, If I leave my cell as General it excepts the number but does not have the slashes. Thanks for your help. ANN -- AG On Thu, 8 Sep 2005 10:35:03 -0700, "AG" <AG@discussions.microsoft.com> wrote: >I want to have a column to be my date field. I want to be able to enter for >instance 09072005 or 90705 I would like for Excel to be able to convert it t...
rounding the answer of a formula
I want to divide one number by another and round the answer up to the nearest hundreth. Ie, 38 inches divided by 12 equals 3.17. Right now I can DISPLAY the 3.17 but when using that in a formula it's calculating with 3.1667 Thanks in advance Boze =CEILING(A2,0.01) with the formula in A2 -- Regards, Peo Sjoblom "Boze" <zebozette@mailhot.com> wrote in message news:eqNdoyvoHHA.4692@TK2MSFTNGP05.phx.gbl... >I want to divide one number by another and round the answer up to the > nearest hundreth. > Ie, 38 inches divided by 12 equals 3.17. > Righ...
subtracting Dates
Could someone please help me with the below task. A1 = 01-Jan-05 = The first day of the year. B1 = 16-June 05 = Their starting date. C1 = 10-Oct 05 = Their finishing date. example I have a program for my staff when they resign to count how many days they have worked here this year and to make sure they have not take too many days holiday and in cell A1 I have 01-Jan-05 and in cell B1 I have their starting date for example 16-June-05 and in cell C1 I have their finishing date for example 10-Oct-05. Now I am trying to work out a formula in D1 to tell me how many days the employee has w...
Modified By and Modified On fields
I am trying to customize a phone form (will need to do the same to most others) by adding Modified By and Modified On fields. After publishing the form, the fields appear on the form just fine but the lookup to a user list is greyed out. Any ideas? Am I doing something wrong? I found the answer myself. Those are system-generated values and. therefore, cannot be modified by a user "mkatsev" wrote: > I am trying to customize a phone form (will need to do the same to most > others) by adding Modified By and Modified On fields. After publishing the > form, the fields ap...
Moving dates between worksheets
I track my bank balance on two worksheets. The first is a date/debit/credit to track the balance. The second worksheet lists my monthly Standing Orders. At the start of each month I would like to transfer the coming month's debit information from the second worksheet to the first. What date format should I use in the second worksheet, and is it possible to increment by exactly a month each time. eg how can I write an instruction to debit a regular sum on say the 8th day of the month? tks I wonder if you would save yourself a lot of time by spending $20 or so to buy Microsoft Mo...
Array Formula to Pick Average
I'd like to see if an array formula can save a few columns on a spreadsheet. I work at a sewage treatment plant. For regulatory purposes we need to report the highest 7-day moving average concentration for a number of variables in our effluent. We also need to report the highest 7-day geometric mean for coliform bacteria. All within a given month. (The bosses won't be interested in seeing the highest seven-day average that occurred within the first six days of the month.) For years we simply configured a column holding 7-day averages (or 7-day geomean) next to the respective...
I have a workbook with 53 pages (onepage for each week of the year), I want to autofill the date of each weekday across all 53 sheets, You you talking about 53 worksheets, each with a tab? If so, Ctrl+Click the right and left tabs to select them and every tab between. This will Group the worksheets. Any data entered will now be entered in all. When finished, click any tab to ungroup (or right-click and choose Ungroup). -- Greeting from the Gulf Coast! http://myweb.cableone.net/twodays "Gibbo" <anonymous@discussions.microsoft.com> wrote in message news:D7F9351D-8DCC-4333-812...
Problem to modify the size of PlotArea
Hello, I am trying to modify a chart so that it prints in a 20cm x 17,5cm frame. I do understand that this is governed by the plotarea.insideheight and plotarea.insidewidth properties. I also do understant that these properties are read-only. So far my code is the following: With ActiveChart .SizeWithWindow = False .PageSetup.ChartSize = xlScreenSize .PlotArea.Width = 567 .PlotArea.Height = 496 Do While .PlotArea.InsideWidth < 567 .PlotArea.Width = .Pl...
EXE change in data modified
Hi Guys Have a question regarding GP. What can cause a change in the "modified date" to the dynamics.exe file? In theory, something would need to change the file. My best guess would be a service pack would change the date. -- www.fmtconsultants.com "cruesta@gmail.com" wrote: > Hi Guys > > Have a question regarding GP. What can cause a change in the "modified > date" to the dynamics.exe file? > > For GP 9.0 SP 1 the Dynamics.exe modified date is 5/16/2006 which looks to be the date the Service Pack was created (as opposed to the da...
absolute formula in Excel
Hello all we need to use absolute formula in most cells, the way that we know is that use the normal formula and then add $ sign before cell ref. It is a nightmare to correct all cells manually. is there any way to make it fix? Thanks The reason for absolute and relative formulas and bits of formulas is that when you copy them they adjust as you want them to. So at the moment if your cells are giving the correct answer you need to do nothing, if you wish to copy them then you only need to adjust one cell accordingly for each formula type and copy it. Or am I missing something? Regards ...
Modifier Error
Whenever we try to go to modifier it gets the following error. The Modifier is currently unavailable because another user is editing resources in the Forms Dictionary. The Server has already been rebooted but we are still coming up with the same error. Thanks, Hi make sure that you are not sharing the Forms Dictionary with another users. Thanks BS "Jess M." <JessM@discussions.microsoft.com> wrote in message news:C56C6878-9410-4CF5-95DE-DCA0AC50EA55@microsoft.com... > Whenever we try to go to modifier it gets the following error. > > The Modifier is current...
modified timeline graph
I have a table of dates and events (and "phase"), and I want to make a timeline graph: 1-d horizontal line, no (visible) y-axis. I want the distance between the events to be spaced based on the date, and the event names to be shown at each point. The dates should be shown too, either for each event or just based on auto-scaling of the x-axis (e.g. major unit = 3 months). Here's the hard part. The events happen in different "phases", and I want to show the phases graphically somehow. I was thinking this component of the graph would be a bar chart, and I could someho...
How do I get a chart to only display the dates in my table?
I am trying to graph a large group of data. It is a series of dates (not all consecutive) - with the time it takes to do a task on each of the dates. When I graph it - it fills in all the "missing" dates. For instance lets say a set of data would be: Date: 12-1-08 12-5-08 12-6-08 12-6-08 12-12-08 Time: .5 .34 .93 1.2 .25 On my graph it would put a date for every date between 12-1 and 12-12 even if there is no data. Is there any way to have Excel graph only the dates that have data? Change the axis from t...
Formula #6
This is a multi-part message in MIME format. ------=_NextPart_000_0067_01C3D629.9FFCBD90 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello All, I need a formula to transfer a figure from another sheet where I = am looking for the last total of the column higher than zero.. The = formula that is used in the column will not allow me to total at the = bottom of the page thus eliminating the easy transfer of columns. Any = help would be greatly appreciated. Tony ------=_NextPart_000_0067_01C3D629.9FFCBD90 Content-Type: text/htm...
COUNTING DATES #4
How do i write a formula to count the number of lines on a database of information that have a date that matches todays date? Say I have a huge database with 10,000 lines of info and I want it to look at all the info and put a number in a cell that says how many of the lines in the datbase contain todays date. -- na See COUNTIF Worksheet Function in help --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Cody" &l...
Modify name displayed in GAL
Hi all! Is there any way to change the manner in wich user are displayed in GAL? I've been aked to set DislayName to "First Last", but leave "Last First" in GAL - is there any way to do this? Thanx -- R.V. ...
modify online services
My bank recently upgraded their online services to include Direct Statements. Previously I had set up to receive Web statements. Money 2004 Omline services Manager does not give me the option to "modify services" only to "change financial institutions." Any ideas on how I can modify online banking services so I can use background banking to get Direct statements? This is how it works in M03: From account list -> Click manage online services -> click Setup online services, then select the financial institution from the list (assuming the list is updated wi...
Formulas in Comments or Mouseover?
I'm trying to simply my spreadsheet. My goal is to either enter formulas into a comment box or some sort o mouse over function. That way, my sheet isn't crammed with data. Tha data will only appear if I put my mouse over a particular cell. Any help will be greatly appreciated -- pikapika1 ----------------------------------------------------------------------- pikapika13's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1089 View this thread: http://www.excelforum.com/showthread.php?threadid=27681 Hi AFAIK this is not possible in Excel -- Regards Fran...
if blank formula
is there a formula that will do this? formating conditioning / formula is =ifblank(c60),omitpayment:") that is what I have but it is not working. what am I doing wrong? @@ I want, if the cell is blank to show Payment Omitted @@ I'm not completely clear on your question, but I think this is what you're looking for. =IF(ISBLANK(C60),"Payment Omitted","") that formula will test to see if c60 is blank, and if it is, will put "payment omitted." if it is not blank it will return nothing. "Colin2u" wrote: > is there a formula that will ...
Simple Date Range Help
I have a project for work that need data broken up as follows Billed Sales Affiliate International Billed Sales Affiliate Domestic We have special codes for each International and Affiliate, so sorting that will be no problem. I need to know a way to pull the data by just inputting a weekly date range and displaying the total for the week. My supervisor suggested going through the entire backup of excel files for the weeks in the month pulling out everything but what I need then making a pivot table. This seems like a huge process. I know there can be a date range...
Pasting Formulas Without Links
Hi all, Is there a means of pasting formulas from one workbook to another without the formulas linking back to the original workbook? Example: When I do Paste Special>Formulas, I get: ='[Monthly Status Report Template v.2.0.xls]Summary'!$B$2 When what I want is: =Summary'!$B$2 I'm going to have to do this to around 50 spreadsheets, so if I can find an quick way of doing it, it's going to be really useful. Note: there are a number of formulas per worksheet, otherwise I'd just delete it it manually! TIA, SamuelT -- SamuelT ---------------------------------...
GP7.5SP6
We're in the process of upgrading from GP7.5 SP4 to SP6. We are on MSSQL Server 7.0. My supervisor has already implemented the hot fix as describe in MBS (that's a script to update some indexes on the server if someone is using GP7.5 manufacturing on MSSQL7). When I try to import modified forms and reports I've received some errors. For instances some references on the VBA to the GUI forms (i.e. text boxes, buttons) become missing. And on the reports I've been told it couldn't find the table MOP_MO_Variance_DC_Detail_Report_Temp and MOP-Sched_Interface_Report_Before....
Search / Modify / Delete . . .
In �sheet1� I have a database with theses headers �Name, Phone, Address and below are the records rows 2-150. In �sheet2� I have the data entr� form (not VBA) its on the sheet, that has a macro that writes t �Sheet1� (for storage). How can implement a search for a record and return the result t �sheet2�, with the ability to modify or delete from the databas (�sheet1�). Thanking you in advance -- Fabl ----------------------------------------------------------------------- Fable's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=218 View this thread: http://www.ex...
Count cells in a column that contain dates
Column J contains dates in some cells, and text in others. I need to count the number of cells in this one column that contains dates. How can I do this? If the only cells that are in that column that are numeric (like dates), you can use: =count(a:a) (Dates are just numbers formatted nicely in excel.) Cachod1 wrote: > > Column J contains dates in some cells, and text in others. I need to count > the number of cells in this one column that contains dates. How can I do > this? -- Dave Peterson ...
Help with the IF formula
i have created a payroll spreadsheet and now im currently having issues with adding the tax. The tax goes like this: if $346 - $480 deduct $63 and 25c for each dollar over $346 $481 - $672 deduct 96 and 40c for each dollar over $481 i just need help in how i'd go about making a formula like that. there is alot more like that but i'd be able to figure it out i just need to know how i would go creating such a formula? http://www.mcgimpsey.com/excel/variablerate.html -- Regards, Peo Sjoblom (No private emails please, for everyone's benefit keep the discussion in the newsgroup...
Date format #33
I want to have a column to be my date field. I want to be able to enter for instance 09072005 or 90705 I would like for Excel to be able to convert it to read 09/07/2005. If I format my cell to Date it reads as ########, If I leave my cell as General it excepts the number but does not have the slashes. Thanks for your help. ANN -- AG On Thu, 8 Sep 2005 10:35:03 -0700, "AG" <AG@discussions.microsoft.com> wrote: >I want to have a column to be my date field. I want to be able to enter for >instance 09072005 or 90705 I would like for Excel to be able to convert it t...
rounding the answer of a formula
I want to divide one number by another and round the answer up to the nearest hundreth. Ie, 38 inches divided by 12 equals 3.17. Right now I can DISPLAY the 3.17 but when using that in a formula it's calculating with 3.1667 Thanks in advance Boze =CEILING(A2,0.01) with the formula in A2 -- Regards, Peo Sjoblom "Boze" <zebozette@mailhot.com> wrote in message news:eqNdoyvoHHA.4692@TK2MSFTNGP05.phx.gbl... >I want to divide one number by another and round the answer up to the > nearest hundreth. > Ie, 38 inches divided by 12 equals 3.17. > Righ...
subtracting Dates
Could someone please help me with the below task. A1 = 01-Jan-05 = The first day of the year. B1 = 16-June 05 = Their starting date. C1 = 10-Oct 05 = Their finishing date. example I have a program for my staff when they resign to count how many days they have worked here this year and to make sure they have not take too many days holiday and in cell A1 I have 01-Jan-05 and in cell B1 I have their starting date for example 16-June-05 and in cell C1 I have their finishing date for example 10-Oct-05. Now I am trying to work out a formula in D1 to tell me how many days the employee has w...
Modified By and Modified On fields
I am trying to customize a phone form (will need to do the same to most others) by adding Modified By and Modified On fields. After publishing the form, the fields appear on the form just fine but the lookup to a user list is greyed out. Any ideas? Am I doing something wrong? I found the answer myself. Those are system-generated values and. therefore, cannot be modified by a user "mkatsev" wrote: > I am trying to customize a phone form (will need to do the same to most > others) by adding Modified By and Modified On fields. After publishing the > form, the fields ap...
Moving dates between worksheets
I track my bank balance on two worksheets. The first is a date/debit/credit to track the balance. The second worksheet lists my monthly Standing Orders. At the start of each month I would like to transfer the coming month's debit information from the second worksheet to the first. What date format should I use in the second worksheet, and is it possible to increment by exactly a month each time. eg how can I write an instruction to debit a regular sum on say the 8th day of the month? tks I wonder if you would save yourself a lot of time by spending $20 or so to buy Microsoft Mo...
Array Formula to Pick Average
I'd like to see if an array formula can save a few columns on a spreadsheet. I work at a sewage treatment plant. For regulatory purposes we need to report the highest 7-day moving average concentration for a number of variables in our effluent. We also need to report the highest 7-day geometric mean for coliform bacteria. All within a given month. (The bosses won't be interested in seeing the highest seven-day average that occurred within the first six days of the month.) For years we simply configured a column holding 7-day averages (or 7-day geomean) next to the respective...