Date Calculation

I need to write a formula or VB code for the following:

Say Column A contains dates and column B amounts.
In column C, I need to calculate 10% of column B if the 
date in Column a is between 1 April and 30 September. If 
the date in column A is between 1 October and 31 March 
then calculate 15%. The problem is it need to work for any 
year and the date in column a will be in the format of 
dd/mm/yyyy.

Your help will be much appreciated. Many Thanks in advance.
0
anonymous (74722)
1/7/2004 2:07:16 PM
excel 39879 articles. 2 followers. Follow

2 Replies
547 Views

Similar Articles

[PageSpeed] 13

Hi

Into cell C2 enter the formula
=IF(AND(MONTH(A2>3,MONTH(A2<10))),0.1*B2,0.15*B2)
and copy it down


-- 
(When sending e-mail, use address arvil@tarkon.ee)
Arvi Laanemets


"Samir" <anonymous@discussions.microsoft.com> wrote in message
news:04da01c3d527$8e316af0$a001280a@phx.gbl...
> I need to write a formula or VB code for the following:
>
> Say Column A contains dates and column B amounts.
> In column C, I need to calculate 10% of column B if the
> date in Column a is between 1 April and 30 September. If
> the date in column A is between 1 October and 31 March
> then calculate 15%. The problem is it need to work for any
> year and the date in column a will be in the format of
> dd/mm/yyyy.
>
> Your help will be much appreciated. Many Thanks in advance.


0
garbage (651)
1/7/2004 2:49:21 PM
Samar,

Put your variable year in G1 and use this formula

=IF(AND(A1>=DATE($G$1,4,1),A1<=DATE($G$1,9,30)),B1*10%,B1*15%)

-- 

HTH

Bob Phillips
    ... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Samir" <anonymous@discussions.microsoft.com> wrote in message
news:04da01c3d527$8e316af0$a001280a@phx.gbl...
> I need to write a formula or VB code for the following:
>
> Say Column A contains dates and column B amounts.
> In column C, I need to calculate 10% of column B if the
> date in Column a is between 1 April and 30 September. If
> the date in column A is between 1 October and 31 March
> then calculate 15%. The problem is it need to work for any
> year and the date in column a will be in the format of
> dd/mm/yyyy.
>
> Your help will be much appreciated. Many Thanks in advance.


0
bob.phillips1 (6510)
1/7/2004 3:44:26 PM
Reply:

Similar Artilces:

Conditional Formatting w/ Dates
I would like to format a set of cells on a condition using dates. For example, in cell A1 I have 5/23/05. In cell B1 I have 6/19/05. In cell C1 I want to have the condition dependent upon the difference of cells A1 and B1. How do I subtract the dates to give me the condition I am looking for. Let us say the conditions are: less than 7 days is green; greater than 7 days, less than 14 is orange; greater than 14 days is red. -- John F.M. ------------------------------------------------------------------------ John F.M.'s Profile: http://www.excelforum.com/member.php?action=getinfo&use...

highlight cell with colour if date overdue
can anyone tell me please how do i highlight a cell in a spreadsheet with colour if date overdue And how exactly should XL determine that the date was overdue? Take a look at Conditional Formatting in XL Help. If, for instance, the due date is in A1 and the date is overdue if it's in the past, select A1, choose Format/Conditional Formatting... and use something like: CF1: =A1<TODAY() Format1: <patterns>/<color> In article <3B7B11E7-894B-458A-BDE6-7710E0743D31@microsoft.com>, Harvey <Harvey@discussions.microsoft.com> wrote: > can anyone ...

VERY URGENT: storing date in main form
(see previous thread) Sorry in advance for re-posting so soon. I really need to sort this out. When I enter a date into the continuous subform, the date field on the main form gets updated with the most recent date before the field on the subform was updated rather than the most recent date after it has been updated (if that makes sense) I need to somehow requery or save the record but I can't see how to do it. ideas? -- "Loose Change 2nd Edition" has been seen by almost 7 million people on Google video You realize, don't you, that the folks here are volunteers...

how to automate min and max on axis based on calculation
While automating some chart making with code, I am trying to automate the span of time reflected on the x-axis. Auto doesn't work because I need the beginning to be based on an October of a given year. Similarly I have requirements for the max. The appropriate min and max are calculated elsewhere, but I am not sure how to code it to accept the value from a cell. Any help would be much appreciated. TIA This has to be done programmatically. For a ready-made solution see AutoChart Manager http://www.tushar-mehta.com/excel/software/autochart/index.html -- Regards, Tushar Mehta www...

How do I change the date format?
If anyone could help, I would be appreciative... I have 800 rows of data where the date is currently formatted: 1. YYYYMMDD How do I change this to MM/DD/YYYY? 2. Telephone number is currently formatted as 7607236300. How do I change this to 760-723-6300? Thank you for helping me. #1. Select the column with the dates Data|text to columns (xl2003 menus) Choose Fixed widths, but don't include any separator lines. Choose Date (ymd order) and finish up. Change the number format (format|cells|number tab) to the format you want (mm/dd/yyyy). #2. Select the range t...

"Calculate" appears in staus bar
In Excel 2003, what does it mean when "Calculate" appears in the left side of the staus bar. I checked options, and calculate is set to automatic. In most instances, calcuations are automatic. but i have lately had one workbook that switches to manual calculate. But not always. I did open a file that was created in XL2010. But it had no macros. When I see "Calculate" in the status bar, what does it mean? Thanks, Tonso "Tonso" <wthomasss@hotmail.com> wrote in message news:075fbbe4-d91e-405f-a4d4-c5f13ddaea6c@s12g2000yqm.googlegroups.com... > In Excel 2...

formula for age at future date
A1 is date of birth in 01/15/30 format. Need to show how old each person will be (years only) by end of 2010. Then need to indicate Y or N if they will be 65 or older at that time. MEJ try this. A1 is DOB. Type in B1 Today() Type this formula in C1 =YEAR(B1)-YEAR(A1) Type this in D1 =IF(C1>59,"Y","N") This will give you a N for anyone under 60 and a Y for older then 60. Hope this helps, Paul "MEJ" wrote: > A1 is date of birth in 01/15/30 format. Need to show how old each person > will be (years only) by end of 2010. Then need to indicate...

Difference between % Gain and Total Return for All Dates?
Hi- Could someone tell me what the difference is between % Gain and Total Return for all dates? Which tends to be a more accurate picture of my portfolio performance? Thanks- -Dan ...

how do i format multiple cells to calculate a sum?
hi there, im am relatively new to excel and use it only to enter and store herd lists for my farming. i have to enter a large amount of information for each cow but have been told that there is a way to automate some of the calculations that need to be made. here is an example HERD NO M/F COL D--O--B First Check date 10265 M B 05/03/02 01/10/02 the first check date is calculated by the sum "=(E2+210)" Where E2 is the D.O.B. and 210 being days. the calculation gives the result simply as a date. my question is that if i enter the nessesary information su...

WTF?? Converting dates from XP to 2003
Ouch...When I read in a spreadsheet in XP, the dates and months are opposite and all my date data gets messed up. Seems that one is fomatted as dd-mm-yyyy, and the other is mm-dd-yyyy. What is going on here, and how to fix it? Hi what kind of file are you importing? -- Regards Frank Kabel Frankfurt, Germany TomFoolery wrote: > Ouch...When I read in a spreadsheet in XP, the dates and months are > opposite and all my date data gets messed up. > > Seems that one is fomatted as dd-mm-yyyy, and the other is mm-dd-yyyy. > > What is going on here, and how to fix it? Probabl...

stopping the date from changing in Excel (and Google Spreadsheets)
Wehn I enter "October 10, 2006" into my excel spreadsheet, it always changes it to "10-10" or something like that. Where can I set my excel spreadsheet to display the full date? Also, does anyone have any experience with Google spreadsheets? Any good? Go to Format, Cells, Number, Date and pick the date format that works for you. Carole o "Flicker686" wrote: > Wehn I enter "October 10, 2006" into my excel spreadsheet, it always changes > it to "10-10" or something like that. Where can I set my excel spreadsheet to > display the...

Charting Dated Data
I have a small file, using only 6 rows and 6 columns. A1 has the word Date in it, A2 through A6 has the dates, 1/1/08, 1/8/08, 1/15/08, 1/22/08, and 1/29/08. B2 through B6 has person's names and the cells from B2 through E6 contains numerical data. I am attempting to create a column chart, with the weekly dates on the X axis and the names in the legend, each column represents an individual. The problem is I am getting a lot of dates between the entered dates, every other day is showing up on the X axis, cluttering up the axis and setting the column widths very narrow. The gap wid...

Filtering Excel 2003 Charts to a date range
I have multiple charts that we look for trends on over 1-2 year time frames. Is it possible to have a chart show all the data that has been entered and when looking at the chart have a date range data to format that range differently. What I am trying to do is we have weekly or montly meetings, in these meetings I would like to show all the additional data differently that has been added since our last meeting, while showing accumulative data in a different format. The easiest way to get different formats is to use different series. If you had one series, you could use autofiltering to...

I enter date 082305 xl changes it to 5/4/25
I set the date format for the cell as: 8/23/05. When I enter date 82305, I get 5/4/25. I've checked everything I can think of but I haven't a clue as to what is causing this. Thanks for all help, "Monalisa18" wrote: > I set the date format for the cell as: 8/23/05. When I enter date 82305, I > get 5/4/25. I've checked everything I can think of but I haven't a clue as to > what is causing this. Thanks for all help, Monalisa You will have to enter the slashes to get the correct date. Excel calculates date from the 1/1/1901 and this is given the refe...

Date Modified sort issue.
Good afternoon. The issue is that when I sort (right-click --> arrange icons by --> Modified or click on the Date Modified header), it sorts by Date Modified, but categorizes by the Name (Folders, Shortcuts, Files. etc.). I was wondering if anyone knew of a way to sort an entire folder (either ascending or decending) by Date Modified. Thank you, WHEELS -------- Original-Nachricht -------- > Good afternoon. > > The issue is that when I sort (right-click --> arrange icons by --> Modified > or click on the Date Modified header), it sorts by Da...

Invalid Argument Error when using 1/1/08 as a date
Hi all, Not sure if this is a known issue - can anyone else create a record that contains a date field with the value of 1/1/08 or 1/1/07, save it and then open it again without getting an "Invalid Argument" error. I am able to produce this error across different environments, different entities and different date fields. Would be interesting to hear more about this. Thanks Hi there, Just to let you know, I just created a task in CRM 4 and set the due date to 1/1/08 and it worked without any issues. This is a field displayed as a Date/Time box, on not just Date. I don...

Calculating Time 05-21-10
Hi I'm setting up a timesheet but when I enter the format to + or - if the 'time' is negative the figures are not showing - is there a way around this? Thanks guys Only by turning on the 1904 date system, which I wouldn't suggest :) -- Regards Dave Hawley www.ozgrid.com "Fie M-C" <FieMC@discussions.microsoft.com> wrote in message news:8071D879-166B-4F82-B949-76066EBA17EE@microsoft.com... > Hi > I'm setting up a timesheet but when I enter the format to + or - if the > 'time' is negative the figures are not showi...

Auto moving by Date in Outlook 2002
Does anyone know if you can tell Outlook to move emails older than 90 days to a folder you specify in Outlook and then in another 30 days delete the emails in that folder? I'm running Exchange Server 2000 on the server and Outlook 2002 on the client pc's. I can't find anyway in Outlook to do this using the rules wizard or Auto Archive. I don't think there are setting to tell Exchange to do this instead of Outlook. But I'm sure there has to be a way. Any ideas? Much Appreciated. Lisa ...

More Date problems
I have a macro that deletes returned records older than 7 days. It first sorts the data into returned date order then checks to see if the first cell is empty (no records returned) and if so, re-sorts the data into the original order (a different date) and quits. If the first cell contains a date less than 7 days old ie date >today()-7, again it re-sorts and quits, otherwise it deletes the data older than 7 days, re-sorts the remaining data and quits. At least that what is supposed to happen The line to check the age of the date in the macro below doesn't work. G6 contains a control ...

Formulas to (a) pull month, year and (b) sum within date range?
I have a list of dates in column A, and numbers down cols B-E. Off to the right, I need a table that lists the month/year and the totals for each of the columns. It needs to be a separate table, rather than a pivot table, so it can be copied-and-pasted by others. What I _think_ I need is: -- a formula in H2:H10 (maybe more) that will look down the full range of dates (call it A2:A100) and put one month/year per row >> H2 = 02/09 H3 = 03/09 H4 = 04/09 etc. -- a formula in I2 that will sum all entries in B2:B100 with the month and year of H2 I think I could get the secon...

Automatic OverTime Calculation #2
Based on a bi-weekly payroll, if an hourly employee fills in 41 hours the first week and 39 hours the second week then I would expect to see 1 hour of overtime for the first week. Instead the software will not calculate overtime in this case since the hours add up to 80. Overtime needs to be applied to each week of the bi-weekly pay period. ---------------- 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 th...

changing date formats
i need to process data in excel from a 3rd party report that gives th timefor different departments in the format dd:hh:mm:ss and i need t be able to get excel to work out the time in hours and minutes so i ca analyse it further thnx in advanc -- Message posted from http://www.ExcelForum.com On Sun, 1 Feb 2004 04:19:57 -0600, chris.howes <<chris.howes.10y0f7@excelforum-nospam.com>> wrote: >i need to process data in excel from a 3rd party report that gives the >timefor different departments in the format dd:hh:mm:ss and i need to >be able to get excel to work out t...

How do I create an alert of expiration date in Excel?
I created a spreadsheet to track documents. The last column is for expiration dates of documents. How can I set up an alert when the expiration date is approaching? Use conditional formatting in the cell. For example, with the date in A12, something like Formula is =TODAY()+7>=$A$12 as the condition. Then choose a pattern. "kachnycz" wrote: > I created a spreadsheet to track documents. The last column is for > expiration dates of documents. How can I set up an alert when the expiration > date is approaching? ...

The no. of decimal places used in a calculation
Without usign ROUNDUP or DOWN , is it possible to calculate a formul using the figure shown in the cell (i.e 2 decimal places) rather tha the underlying figure which could be to many more places? How many decimal places does Excel work to -- Message posted from http://www.ExcelForum.com Hi I believe Excel works to 15 decimal places. If you only want to use the places shown on your sheet you'll need to go to Tools / Options / Calculation and check 'Precision as displayed'. Be warned, this will lose all the other decimal places and you cannot undo this to get them back! -- And...

Disable calculation while spinning
A spreadsheet has a Spin button that is linked to a cell. I want th user to have the possibility of either to spin or to enter a valu directly in the linked cell. So far, so good. Except that th spreadsheet recalculates every time whilst spinning. As the amount o calculation is quite important, the spinning is seriously slowed down and the spreadsheet user's blood pressure is accordingly up. ;) I tried implementing a boolean on the spreadsheet module level and t test/toggle it at the change/spinup or down event. But I hit upon logical conundrum in that the order of events is first Change...