Rounding date field up to next first of month, with existing formu
I have a spreadsheet that has the following 4 columns. Term End Date Evg Term Days Notice Next Avail Termination Date The column "Next Avail Termination Date" has a formula that has the current logic applied: If Todays Date is greater than the Term End Date minus the Days Notice, and the Evg Term = MO, then the Next Avail Termination Date = Today plus Days Notice. This is working fine with this formula: =IF(AND(TODAY()>A2-C2,B2="mo"),TODAY()+C2) However, I need to add one more piece of formula, and I can't quite figure out how. I need it to add logic, that if the date returned from the current formula above is any day other than the first of the month, that it will round to the first of the next month. For instance, using the formula above, it's currently returning the date "02/12/10", and I need it to instead return "03/01/10". Any help is greatly appreciated. Thanks!
|
0 |
|
Utf
1/14/2010 4:45:01 AM
excel.worksheet.functions
4936 articles. 
2 followers.
5 Replies
1161 Views
"Stacie2410" wrote: > I have a spreadsheet that has the following 4 columns. > > Term End Date Evg Term Days Notice Next Avail Termination Date > > The column "Next Avail Termination Date" has a formula that has the current > logic applied: > > If Todays Date is greater than the Term End Date minus the Days Notice, and > the Evg Term = MO, then the Next Avail Termination Date = Today plus Days > Notice. This is working fine with this formula: > > =IF(AND(TODAY()>A2-C2,B2="mo"),TODAY()+C2) > > However, I need to add one more piece of formula, and I can't quite figure > out how. I need it to add logic, that if the date returned from the current > formula above is any day other than the first of the month, that it will > round to the first of the next month. For instance, using the formula above, > it's currently returning the date "02/12/10", and I need it to instead return > "03/01/10". > > Any help is greatly appreciated. Thanks! My apologies, the columns didn't show up quite right, they are: Column A: Term End Date Column B: Evg Term Column C: Days Notice Column D: Next Avail Termination Date
|
|
0 |
|
Utf
1/14/2010 4:51:02 AM
Hi =IF(AND(TODAY()>A2-C2,B2="mo"),DATE(YEAR(TODAY()+C2-1),MONTH(TODAY()+C2-1)+1,1)) Arvi Laanemets "Stacie2410" <Stacie2410@discussions.microsoft.com> wrote in message news:F1F6600F-D366-40A2-99EC-B77C8C78D52D@microsoft.com... >I have a spreadsheet that has the following 4 columns. > > Term End Date Evg Term Days Notice Next Avail Termination Date > > The column "Next Avail Termination Date" has a formula that has the > current > logic applied: > > If Todays Date is greater than the Term End Date minus the Days Notice, > and > the Evg Term = MO, then the Next Avail Termination Date = Today plus Days > Notice. This is working fine with this formula: > > =IF(AND(TODAY()>A2-C2,B2="mo"),TODAY()+C2) > > However, I need to add one more piece of formula, and I can't quite figure > out how. I need it to add logic, that if the date returned from the > current > formula above is any day other than the first of the month, that it will > round to the first of the next month. For instance, using the formula > above, > it's currently returning the date "02/12/10", and I need it to instead > return > "03/01/10". > > Any help is greatly appreciated. Thanks!
|
|
0 |
|
Arvi
1/14/2010 6:04:06 AM
Maybe this: =IF(AND(TODAY()>A2-C2,B2="mo"),DATE(YEAR(A2),MONTH(A2),1),DATE(YEAR(A2),MONTH(A2)+1,1)) Does that help? -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Stacie2410" wrote: > > > "Stacie2410" wrote: > > > I have a spreadsheet that has the following 4 columns. > > > > Term End Date Evg Term Days Notice Next Avail Termination Date > > > > The column "Next Avail Termination Date" has a formula that has the current > > logic applied: > > > > If Todays Date is greater than the Term End Date minus the Days Notice, and > > the Evg Term = MO, then the Next Avail Termination Date = Today plus Days > > Notice. This is working fine with this formula: > > > > =IF(AND(TODAY()>A2-C2,B2="mo"),TODAY()+C2) > > > > However, I need to add one more piece of formula, and I can't quite figure > > out how. I need it to add logic, that if the date returned from the current > > formula above is any day other than the first of the month, that it will > > round to the first of the next month. For instance, using the formula above, > > it's currently returning the date "02/12/10", and I need it to instead return > > "03/01/10". > > > > Any help is greatly appreciated. Thanks! > > My apologies, the columns didn't show up quite right, they are: > > Column A: Term End Date > Column B: Evg Term > Column C: Days Notice > Column D: Next Avail Termination Date
|
|
0 |
|
Utf
1/14/2010 6:15:01 AM
Just think what you have to do. One way is . . . If DAY(MyDate) = 1 then MyDate, else MyDate = DATE(YEAR(MyDate), MONTH (MyDate) + 1, 1). But of course December (where one has to increment year) have to be dealt with. But EOMONTH() function gives the date of the last day of a month, so add one to that date. =IF(DAY(MyDate)=1, MyDate, EOMONTH(MyDate, 1)+1) MyDate is, of course, the date you have calculated previously. Look up EOMONTH() in Excel Help. Also look up EDATE() function & other date functions. Alan Lloyd
|
|
0 |
|
alanglloyd
1/14/2010 6:18:48 AM
On Wed, 13 Jan 2010 20:45:01 -0800, Stacie2410 <Stacie2410@discussions.microsoft.com> wrote: >I have a spreadsheet that has the following 4 columns. > >Term End Date Evg Term Days Notice Next Avail Termination Date > >The column "Next Avail Termination Date" has a formula that has the current >logic applied: > >If Todays Date is greater than the Term End Date minus the Days Notice, and >the Evg Term = MO, then the Next Avail Termination Date = Today plus Days >Notice. This is working fine with this formula: > >=IF(AND(TODAY()>A2-C2,B2="mo"),TODAY()+C2) > >However, I need to add one more piece of formula, and I can't quite figure >out how. I need it to add logic, that if the date returned from the current >formula above is any day other than the first of the month, that it will >round to the first of the next month. For instance, using the formula above, >it's currently returning the date "02/12/10", and I need it to instead return >"03/01/10". > >Any help is greatly appreciated. Thanks! Subtract 1 from the calculated date Go to end of month Add 1 So modifying your formula: =IF(AND(TODAY()>A2-C2,B2="mo"),EOMONTH(TODAY()+C2-1,0)+1) If you have Excel prior to 2007, you will need to ensure the Analysis ToolPak is installed. See HELP for the EOMONTH function if this formula returns a #NAME error. --ron
|
|
0 |
|
Ron
1/14/2010 11:14:00 AM
Similar Artilces:
VBA Show in red if Date > another date
Hi, Small Problem I think Because the user can delete Rows in the worksheet I can not use the normal Conditional formating. I think the VBA is the solution Col "A" have a date, example: 2009-01-25 The user will add a date in Col "P" to close the lead. Example: 2009-03-02 I need to highlight the date in Column "P" if it is 45 days over the date in Col "A". The only way I can see is using the VBA. Like I said, I need to select the col ("A:A") and ("P:P") because the user can sometimes delete some rows. Can someone h...
Deleting Custom Fields created in SQL Schema
Hi, I read in another thread that you cannot delete custom fields created in SQL Schemas. Will this be something available in the next version of SQL or CRM 5.0 or how or when can this be done. Concerned as we will have to create a large number of custom fields for an upcoming conversion and if we need to delete any then we are "hooped". Also, what is the limit to the number of custom fields you can add? Thanks! Shauna I am not sure on the next version of CRM if it will allow that or not. Currently there is no way to delete fields. The limit is not really a limit of CRM...
date popping up in error
I have a very large excel file with multiple tabs, hundreds of if statements, some simple calculations, graphs and conditional formatting. Now when doing data entry I get today’s date popping up in a cell that is not formatted and when I go to print preview its not there. Why is this happening? Slow down a bit. You say "Now when doing data entry...". What are you doing? Exactly? In what cell(s)? Don't just say you're entering data. What data? You say "I get today’s date popping up in a cell.." What cell? The active cell? What sheet? The acti...
Extract Date from Date & Time Cell
Can someone help with this please I need to extract the date portion from a cell that contains a date & time. I need this "date only" cell to be within a range that I output to access. However changing the format of the cell only seems to change what is viewed and the time is still outputted to access which I dont want Isnt there a format statement or something I can do within this cell to prevent the time portion to be outputted as well? Thanks in Advance Scott if the cell is formatted as general, you will see a number of the format nnnnn.nnnnn. The part before the deci...
Weird meeting accepted email date
Recently we found that 1 particular individual's accepted meeting email reply has a sent date of 12/31/1969 7:00PM. It has happened twice in the past week. This meeting accepted email reply was sent from a Blackberry. Has anyone encountered this problem? Thanks. In news:D3EA6D5C-4FCC-4057-AB63-9791839B7ED1@microsoft.com, wli2k2 <wli2k2@discussions.microsoft.com> typed: > Recently we found that 1 particular individual's accepted meeting > email reply has a sent date of 12/31/1969 7:00PM. It has happened > twice in the past week. This meeting accepted email re...
remove dates from chart
I am new at creation of charts. I do not have the weekends in my data but they keep showing up in my chart? Can they be removed? Thanks In a line or column chart, the default axis type when Excel recognizes dates in a time-scale axis. Change this to a category axis: Chart menu > Chart Options > Axes tab, select Category under Primary Category (X) Axis. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Rebecca" <Rebecca@discussions.microsoft.com> wrote in message news:CBCC4D58-5B39-45DC-9B79-0861034B7BC0@...
using dates pre-1900?
I need to have Excel and Access recognise dates between 1800 and 1910, in order to perform time lapse calculations and to sort entries in date order. Is there a way to force these programmes to recognise such dates? http://j-walk.com/ss/excel/files/xdate.htm -- Kind Regards, Niek Otten Microsoft MVP - Excel "Historysue" <Historysue@discussions.microsoft.com> wrote in message news:0200C317-673F-45A3-A896-8DA97E1718FB@microsoft.com... >I need to have Excel and Access recognise dates between 1800 and 1910, in > order to perform time lapse calculations and to sort e...
Cell showing date and time
I have a cell set up as format dd/mm/yyyy hh:mm:ss and the contents are 01/01/1995 00:00:00 In the cell below I put 01/01/1995 01:00:00 then fill the cells for a few hundred. After about 200 cells the value shows as 09/01/1995 10:00:01 - in other words the increment being added for each cell is slightly greater than the desired one hour. I assume this is a lack of precision in the cell value. Is there a way of changing this? I am using Excel 2007. Grateful for advice. Enter the first date/time in A1 and in A2 enter: =A2+TIME(1,0,0) and copy down -- Gary'...
round() ?
Hello How can i round float to 2 digits after '.' ? Thanx Quick and Dirty: Multiply by 100, add 0.5 (to round up correctly). convert to long, then back to float, then divide by 100. This is limited to values that will fit within a long, and is not efficient. It is reliable. also: float val; val = val - fmod(val, .01); Or, the real thing: char *_fcvt( double value, int count, int *dec, int *sign ); Which is a char string, which you can then feed to atof() and get your rounded value back. Alas, beware errors in precision limits! Balboos user@domain.invalid wrote: >...
DATE Entry
Several persons are using my spreadsheet and they enter the date in different formats (12/11/2003 or 1.2.03). This date is used in a calculation and requires the format DD/MM/YYYY. Is there a way that I can reject a date that is not in the required format. Thank you Jim From an answer I gave in another groupd 2 days ago Normally all that is needed to change from US <-> Rest of World dates scheme is to use Format|Cells|Dates, But I suspect the dots in you dates are causing a problem. You may need to extract the three numeric values and make a 'date' out of them. Here is one...
Wrong Date #2
Sent e-mail. It was replied to in the same day, but the message header said the date was 5 days into the future. This put the e-mails at the top of my e-mail listing. Any clues why this happened? I tried to change the message header date, but Outlook would not let me. thanks, ...
populate field from combo box (again)
sorry, can't seem to wrap my mind around this and i know its simple i have an input form for property details - PropertyInputFrm i have a table with suburb, state and postcode - PostCodesTbl i want to be able to select the suburb with a combo box and have the state and postcode populate automatically i'm thinking its Dlookup -- deb Are you absolutely certain that the same postal code is not used by more than one suburb? (I seem to recall running into a local postal code that applied to two "cities"...) If you are trying to do this so that the table ...
rounding #9
is there a way to have a column round automatically instead of having to round onto another column? I don't want to add another column to just be able to round. -- denhar ------------------------------------------------------------------------ denhar's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24089 View this thread: http://www.excelforum.com/showthread.php?threadid=378356 If the column contains formulas, the formulas can incorporate the Round() function within themselves. For example, if the formula is: =A1*B1 it could be revised to: =ROUND(A1*...
Query to get Sales by Date
I use Excel to query into RMS database. I want to create a spreadsheet from which I can graph daily and weekly movement of total sales. I have 2 stores so I need to separate each store figures also. sammm wrote: > I use Excel to query into RMS database. I want to create a spreadsheet from > which I can graph daily and weekly movement of total sales. I have 2 stores > so I need to separate each store figures also. you can use the query available in the file qrp of sales .. cod Hi there, Retail Analytics from PRofessional Advantage will enable you to create that report using E...
sendobject, emal bcc recipients from field
Hello all, I am not a programmer, although I have been picking things up along the way, so if any of my explanations sound rudimentary I apologize in advance. I have designed a database to keep track of my students, each student record includes things like a Student ID (primary key), firstname, lastname, email address, and various other contact information. I want to be able to have a control on my form which allows me to email my students with the click of a button. I am using the SendObject function to open a new email, I then select acSendNoObject. What I want to be able to do ...
The e-mail account does not exist at the organization this message was sent to #2
We have a user who has a problem when she creates meetings within Outlook. When a recipient accepts or declines then the following message is returned: Your message did not reach some or all of the intended recipients. Subject: Accepted: Test please ignore Sent: 23/01/2007 11:08 The following recipient(s) could not be reached: Jennifer Laing on 23/01/2007 11:08 The e-mail account does not exist at the organization this message was sent to. Check the e-mail address, or contact the recipient directly to find out the correct address. This person used to work f...
Advancing the Date Function?
Hi, I'm just looking for a function that looks at the previous cell, into which I've typed the date (in MM-yy format), and then simply advances it to the next month (i.e I type in Jan-10, then the next cell automatically displays Feb-10, and the one after Mar-10, etc). Sorry if this seems a simple request. Thanks As long as you typed in a real date, the format won't matter to excel -- it will matter to you! But if you have June 1, 2010 in A1 (formatted to 06-10), you can use this in B1: =date(year(a1),month(a1)+1,1) and format it the way you like. Ruper...
Changing Start Date affects Duration and/or Work in 2007 (not in 2003)
I've searched all over for this strange behavior but I cannot find anything about it... In my plan there are only Tasks with Type=Fixed Duration and Effort Driven=No. Some Tasks have one of more Resources (type=Work) connected with Calendar=Project Calendar. One of the earliest tasks, planned to be started back in January will probably start now next week (from 22nd Feb), so while updating the plan I have to change that Start Date and after that all FS connected tasks will start at least as many days later as the duration of the changed task. When doing this in Project 2003 t...
email receive date and time changes
I don't know if this is an exchange or outlook problem. but my email receive date and time, and whether marked as unread constantly updates. I read the emails then some time later look at inbox and all the emails have new current date and time and are marked unread. how odd I'd appreaciate any help Jon Please provide the following information: 1) Version of Outlook 2) Email environment: Exchange, POP3, etc. 3) Does the user have a PST? If so, what is the size? 4) Are there any error messages/numbers? 5) Have you checked the application event viewer for any Outlook or Off...
Rounding question
I'm using Excel 2007 but 'save as' 97-2003 as colleagues have different versions. All number formats are set to 2 decimal places. I'm finding that percentage calculations are rounding up to 2 decimal places but when the result in currency is subtracted from another figure the sum is rounded down. This gives results such as 6 - 3 = 4. I'm sure there must be a simple answer but I can't find it. Hoping someone here knows, as checking all simple calcuations is getting ridiculous ! Carrie "Carrie" <pantscarolyn.smith2@ntlworld.com> w...
char exist in font
Hi, I would like to know if there is a way to check if a character exists in a specific font for example: the character U+2205 does not exist in Arial , but does exist in Arial Unicode. thanks, Yoav. > I would like to know if there is a way to check if a character exists in a > specific font > for example: the character U+2205 does not exist in Arial , but does exist > in Arial Unicode. Several options: GetGlyphIndices (but does not work with characters beyond BMP) or Uniscribe - ScriptGetCMap or do your own cmap parsing -- Mihai Nita [Microsoft MVP, Vis...
Date Range 03-22-07
Tp pull a monthly report I have under criteria of Query: "Between [BeginningDate] And [EndingDate]". When Prompted I can enter Beginning, then End Date. I would like to also include an option where I can leave the beginning date blank and End date blank to pull all records. I have a similar Query that pulls all parts of CLient Names: Like "*" & [Enter Client Name, Leave Blank for All] & "*".... When I am trying to get this to work for my dates, I either Pull All records no matter the date ranges I enter, Or, I pull no records.... I would also l...
Changes to requested ship date in SOP should roll down to lines
When changing the requested ship date at the header on open SOP orders you should have the option to roll this change down to existing SOP lines. -- Jim@TurboChef ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/News...
Problem with rounding currency values
Hi, I am using the Sales Global Procedure SOP_Calculate_Trade_Discount_Split. This procedure returns the line trade discount. The line trade discount returned is rounded off. eg: if value is 6.76 then it returns 7.00. Is there some place where i can specify to turn off the rounding off? The output type is Currency. Any help is greatly appreciated. Thanks & Regards, Sup ...
Round Up and Round Down Time
Good Morning & a Happy New Year to all subscribers in this group. I want to round up Start times and round down Finish times to the next or previous 15 minutes in my spreadsheet. I have found MROUND but this does not work for me in all cases. There does not seem to be MROUNDUP or MROUNDDOWN available. I can get the results I want using a VLOOKUP table but is there another way? Regards to all, Dave Moore =FLOOR(A1,TIME(,15,)) =CEILING(A1,TIME(,15,)) -- David Biddulph DaveMoore wrote: > Good Morning & a Happy New Year to all subscribers in this group. > >...
Hi, Small Problem I think Because the user can delete Rows in the worksheet I can not use the normal Conditional formating. I think the VBA is the solution Col "A" have a date, example: 2009-01-25 The user will add a date in Col "P" to close the lead. Example: 2009-03-02 I need to highlight the date in Column "P" if it is 45 days over the date in Col "A". The only way I can see is using the VBA. Like I said, I need to select the col ("A:A") and ("P:P") because the user can sometimes delete some rows. Can someone h...
Deleting Custom Fields created in SQL Schema
Hi, I read in another thread that you cannot delete custom fields created in SQL Schemas. Will this be something available in the next version of SQL or CRM 5.0 or how or when can this be done. Concerned as we will have to create a large number of custom fields for an upcoming conversion and if we need to delete any then we are "hooped". Also, what is the limit to the number of custom fields you can add? Thanks! Shauna I am not sure on the next version of CRM if it will allow that or not. Currently there is no way to delete fields. The limit is not really a limit of CRM...
date popping up in error
I have a very large excel file with multiple tabs, hundreds of if statements, some simple calculations, graphs and conditional formatting. Now when doing data entry I get today’s date popping up in a cell that is not formatted and when I go to print preview its not there. Why is this happening? Slow down a bit. You say "Now when doing data entry...". What are you doing? Exactly? In what cell(s)? Don't just say you're entering data. What data? You say "I get today’s date popping up in a cell.." What cell? The active cell? What sheet? The acti...
Extract Date from Date & Time Cell
Can someone help with this please I need to extract the date portion from a cell that contains a date & time. I need this "date only" cell to be within a range that I output to access. However changing the format of the cell only seems to change what is viewed and the time is still outputted to access which I dont want Isnt there a format statement or something I can do within this cell to prevent the time portion to be outputted as well? Thanks in Advance Scott if the cell is formatted as general, you will see a number of the format nnnnn.nnnnn. The part before the deci...
Weird meeting accepted email date
Recently we found that 1 particular individual's accepted meeting email reply has a sent date of 12/31/1969 7:00PM. It has happened twice in the past week. This meeting accepted email reply was sent from a Blackberry. Has anyone encountered this problem? Thanks. In news:D3EA6D5C-4FCC-4057-AB63-9791839B7ED1@microsoft.com, wli2k2 <wli2k2@discussions.microsoft.com> typed: > Recently we found that 1 particular individual's accepted meeting > email reply has a sent date of 12/31/1969 7:00PM. It has happened > twice in the past week. This meeting accepted email re...
remove dates from chart
I am new at creation of charts. I do not have the weekends in my data but they keep showing up in my chart? Can they be removed? Thanks In a line or column chart, the default axis type when Excel recognizes dates in a time-scale axis. Change this to a category axis: Chart menu > Chart Options > Axes tab, select Category under Primary Category (X) Axis. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Rebecca" <Rebecca@discussions.microsoft.com> wrote in message news:CBCC4D58-5B39-45DC-9B79-0861034B7BC0@...
using dates pre-1900?
I need to have Excel and Access recognise dates between 1800 and 1910, in order to perform time lapse calculations and to sort entries in date order. Is there a way to force these programmes to recognise such dates? http://j-walk.com/ss/excel/files/xdate.htm -- Kind Regards, Niek Otten Microsoft MVP - Excel "Historysue" <Historysue@discussions.microsoft.com> wrote in message news:0200C317-673F-45A3-A896-8DA97E1718FB@microsoft.com... >I need to have Excel and Access recognise dates between 1800 and 1910, in > order to perform time lapse calculations and to sort e...
Cell showing date and time
I have a cell set up as format dd/mm/yyyy hh:mm:ss and the contents are 01/01/1995 00:00:00 In the cell below I put 01/01/1995 01:00:00 then fill the cells for a few hundred. After about 200 cells the value shows as 09/01/1995 10:00:01 - in other words the increment being added for each cell is slightly greater than the desired one hour. I assume this is a lack of precision in the cell value. Is there a way of changing this? I am using Excel 2007. Grateful for advice. Enter the first date/time in A1 and in A2 enter: =A2+TIME(1,0,0) and copy down -- Gary'...
round() ?
Hello How can i round float to 2 digits after '.' ? Thanx Quick and Dirty: Multiply by 100, add 0.5 (to round up correctly). convert to long, then back to float, then divide by 100. This is limited to values that will fit within a long, and is not efficient. It is reliable. also: float val; val = val - fmod(val, .01); Or, the real thing: char *_fcvt( double value, int count, int *dec, int *sign ); Which is a char string, which you can then feed to atof() and get your rounded value back. Alas, beware errors in precision limits! Balboos user@domain.invalid wrote: >...
DATE Entry
Several persons are using my spreadsheet and they enter the date in different formats (12/11/2003 or 1.2.03). This date is used in a calculation and requires the format DD/MM/YYYY. Is there a way that I can reject a date that is not in the required format. Thank you Jim From an answer I gave in another groupd 2 days ago Normally all that is needed to change from US <-> Rest of World dates scheme is to use Format|Cells|Dates, But I suspect the dots in you dates are causing a problem. You may need to extract the three numeric values and make a 'date' out of them. Here is one...
Wrong Date #2
Sent e-mail. It was replied to in the same day, but the message header said the date was 5 days into the future. This put the e-mails at the top of my e-mail listing. Any clues why this happened? I tried to change the message header date, but Outlook would not let me. thanks, ...
populate field from combo box (again)
sorry, can't seem to wrap my mind around this and i know its simple i have an input form for property details - PropertyInputFrm i have a table with suburb, state and postcode - PostCodesTbl i want to be able to select the suburb with a combo box and have the state and postcode populate automatically i'm thinking its Dlookup -- deb Are you absolutely certain that the same postal code is not used by more than one suburb? (I seem to recall running into a local postal code that applied to two "cities"...) If you are trying to do this so that the table ...
rounding #9
is there a way to have a column round automatically instead of having to round onto another column? I don't want to add another column to just be able to round. -- denhar ------------------------------------------------------------------------ denhar's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24089 View this thread: http://www.excelforum.com/showthread.php?threadid=378356 If the column contains formulas, the formulas can incorporate the Round() function within themselves. For example, if the formula is: =A1*B1 it could be revised to: =ROUND(A1*...
Query to get Sales by Date
I use Excel to query into RMS database. I want to create a spreadsheet from which I can graph daily and weekly movement of total sales. I have 2 stores so I need to separate each store figures also. sammm wrote: > I use Excel to query into RMS database. I want to create a spreadsheet from > which I can graph daily and weekly movement of total sales. I have 2 stores > so I need to separate each store figures also. you can use the query available in the file qrp of sales .. cod Hi there, Retail Analytics from PRofessional Advantage will enable you to create that report using E...
sendobject, emal bcc recipients from field
Hello all, I am not a programmer, although I have been picking things up along the way, so if any of my explanations sound rudimentary I apologize in advance. I have designed a database to keep track of my students, each student record includes things like a Student ID (primary key), firstname, lastname, email address, and various other contact information. I want to be able to have a control on my form which allows me to email my students with the click of a button. I am using the SendObject function to open a new email, I then select acSendNoObject. What I want to be able to do ...
The e-mail account does not exist at the organization this message was sent to #2
We have a user who has a problem when she creates meetings within Outlook. When a recipient accepts or declines then the following message is returned: Your message did not reach some or all of the intended recipients. Subject: Accepted: Test please ignore Sent: 23/01/2007 11:08 The following recipient(s) could not be reached: Jennifer Laing on 23/01/2007 11:08 The e-mail account does not exist at the organization this message was sent to. Check the e-mail address, or contact the recipient directly to find out the correct address. This person used to work f...
Advancing the Date Function?
Hi, I'm just looking for a function that looks at the previous cell, into which I've typed the date (in MM-yy format), and then simply advances it to the next month (i.e I type in Jan-10, then the next cell automatically displays Feb-10, and the one after Mar-10, etc). Sorry if this seems a simple request. Thanks As long as you typed in a real date, the format won't matter to excel -- it will matter to you! But if you have June 1, 2010 in A1 (formatted to 06-10), you can use this in B1: =date(year(a1),month(a1)+1,1) and format it the way you like. Ruper...
Changing Start Date affects Duration and/or Work in 2007 (not in 2003)
I've searched all over for this strange behavior but I cannot find anything about it... In my plan there are only Tasks with Type=Fixed Duration and Effort Driven=No. Some Tasks have one of more Resources (type=Work) connected with Calendar=Project Calendar. One of the earliest tasks, planned to be started back in January will probably start now next week (from 22nd Feb), so while updating the plan I have to change that Start Date and after that all FS connected tasks will start at least as many days later as the duration of the changed task. When doing this in Project 2003 t...
email receive date and time changes
I don't know if this is an exchange or outlook problem. but my email receive date and time, and whether marked as unread constantly updates. I read the emails then some time later look at inbox and all the emails have new current date and time and are marked unread. how odd I'd appreaciate any help Jon Please provide the following information: 1) Version of Outlook 2) Email environment: Exchange, POP3, etc. 3) Does the user have a PST? If so, what is the size? 4) Are there any error messages/numbers? 5) Have you checked the application event viewer for any Outlook or Off...
Rounding question
I'm using Excel 2007 but 'save as' 97-2003 as colleagues have different versions. All number formats are set to 2 decimal places. I'm finding that percentage calculations are rounding up to 2 decimal places but when the result in currency is subtracted from another figure the sum is rounded down. This gives results such as 6 - 3 = 4. I'm sure there must be a simple answer but I can't find it. Hoping someone here knows, as checking all simple calcuations is getting ridiculous ! Carrie "Carrie" <pantscarolyn.smith2@ntlworld.com> w...
char exist in font
Hi, I would like to know if there is a way to check if a character exists in a specific font for example: the character U+2205 does not exist in Arial , but does exist in Arial Unicode. thanks, Yoav. > I would like to know if there is a way to check if a character exists in a > specific font > for example: the character U+2205 does not exist in Arial , but does exist > in Arial Unicode. Several options: GetGlyphIndices (but does not work with characters beyond BMP) or Uniscribe - ScriptGetCMap or do your own cmap parsing -- Mihai Nita [Microsoft MVP, Vis...
Date Range 03-22-07
Tp pull a monthly report I have under criteria of Query: "Between [BeginningDate] And [EndingDate]". When Prompted I can enter Beginning, then End Date. I would like to also include an option where I can leave the beginning date blank and End date blank to pull all records. I have a similar Query that pulls all parts of CLient Names: Like "*" & [Enter Client Name, Leave Blank for All] & "*".... When I am trying to get this to work for my dates, I either Pull All records no matter the date ranges I enter, Or, I pull no records.... I would also l...
Changes to requested ship date in SOP should roll down to lines
When changing the requested ship date at the header on open SOP orders you should have the option to roll this change down to existing SOP lines. -- Jim@TurboChef ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/News...
Problem with rounding currency values
Hi, I am using the Sales Global Procedure SOP_Calculate_Trade_Discount_Split. This procedure returns the line trade discount. The line trade discount returned is rounded off. eg: if value is 6.76 then it returns 7.00. Is there some place where i can specify to turn off the rounding off? The output type is Currency. Any help is greatly appreciated. Thanks & Regards, Sup ...
Round Up and Round Down Time
Good Morning & a Happy New Year to all subscribers in this group. I want to round up Start times and round down Finish times to the next or previous 15 minutes in my spreadsheet. I have found MROUND but this does not work for me in all cases. There does not seem to be MROUNDUP or MROUNDDOWN available. I can get the results I want using a VLOOKUP table but is there another way? Regards to all, Dave Moore =FLOOR(A1,TIME(,15,)) =CEILING(A1,TIME(,15,)) -- David Biddulph DaveMoore wrote: > Good Morning & a Happy New Year to all subscribers in this group. > >...