Question about Microsoft Office Excel 2003 for PC

1 Answer

Calculate Quarters between any two dates

Example: Start Date: 27/03/2006 End Date: 19/08/2006
I need to calculate how many complete three month periods have passed between the two dates - this is not as simple as converting the dates
to a timestamp, defining a year as 60 * 60 * 24 * 365.25 seconds, then dividing by 3, as the length of months do differ, as henceforth quarters will differ.
[while the above would work in a lot of cases, it would sometimes fall due to different lengths of months]. i.e. if you adjust the start dates:

Quarter 1: 1 Feb - 30 April - 89 days
Quarter 2: 1 May - 31 July - 92 days

as per my example- Start Date: 27/03/2006
End Date: 19/08/2006
my quarters will be as 1-4-2006 -> 31-07-2007 i.e 1 Quater and days will be calculated as 31-27=5 (27 inclusive) and ist 18 days (19 exclusive in end date)
final ans 1 Quarter and 23 days

Posted by on

  • eimtiyazahma May 09, 2009

    thanks the_fire but its the solution to problem, it will give interval in months divided by 3. in this scenario we have many 3 month quarter with different days i.e 89,90,91 and 92. because i have calculate days remaining also.

×

Ad

1 Answer

  • Level 3:

    An expert who has achieved level 3 by getting 1000 points

    All-Star:

    An expert that got 10 achievements.

    MVP:

    An expert that got 5 achievements.

    Vice President:

    An expert whose answer got voted for 100 times.

  • Master
  • 432 Answers

Not sure if this is what you are after entirely but should at least help you out some what

=DATEDIF(A3,C3,"m")/3

As you said you cant just divide by 3 but I have divided the formula by 3 to see how many 3 months will go into the output you could maybe have another formula to divide it by 2 to see if it will divide by 2 to see if you get a whole number or not, if you do then you can just return the first part before the dot ( using split function ) and if the value is less then 1 then obviously the 3 months has not passed between the 2 dates

Posted on May 09, 2009

  • Shane Russell
    Shane Russell May 09, 2009

    http://www.cpearson.com/excel/datedif.as...

    Just a website link to show you how to use datedif in excel


  • Shane Russell
    Shane Russell May 09, 2009

    easiest way would be to have the dates split up into 3 columns

    day, month, year and then use the datedif function to calculate the difference between the start and end of each ( day, month and year ) seperately / individually and then you can put it back together as a string afterwards.


×

Ad

1 Suggested Answer

6ya6ya
  • 2 Answers

SOURCE: I have freestanding Series 8 dishwasher. Lately during the filling cycle water hammer is occurring. How can this be resolved

Hi,
a 6ya expert can help you resolve that issue over the phone in a minute or two.
Best thing about this new service is that you are never placed on hold and get to talk to real repairmen in the US.
the service is completely free and covers almost anything you can think of.(from cars to computers, handyman, and even drones)
click here to download the app (for users in the US for now) and get all the help you need.
Goodluck!

Posted on Jan 02, 2017

Ad

Add Your Answer

Uploading: 0%

my-video-file.mp4

Complete. Click "Add" to insert your video. Add

×

Loading...
Loading...

Related Questions:

1 Answer

How do you calculate and charge interest on outstanding accounts


That question is much more complicated than you think - are you charging interest percentage daily? Weekly? Monthly? Anually? Once you have the period figured, you begin at some start point of your choosing. Exactly one "period" later, you multiply the basis (the outstanding balance) by the percentage rate (5%, for example, would mean you multiply by 0.05), then add that number to the basis - that's your new basis, your new outstanding balance.

But... if you charge an annual interest rate, and you compound daily or weekly or monthly, you have to take payments into account and adjust for them - it's fair to charge interest up to the moment of payment, but not beyond that moment; you can rightly only charge interest on the remaining unpaid balance beyond that date.

If you charge an annual interest rate but compound monthly, then every month you'd charge 1/12 of your annual interest rate. If weekly, 1/52. If daily, 1/365. The smaller the compounding period, the easier it is to calculate interest around payments, but the more paperwork is involved.

Jul 14, 2014 | Office Equipment & Supplies

1 Answer

Need a formula to calculate length of service in excel


You could do a simple +NOW-START DATE

It would depend on the format of your start dates - i.e YY, DD/MM/YY as to the results you get.

Do want the results in years, months, days?




Jun 05, 2009 | Microsoft Excel for PC

1 Answer

Need to change the time


You weren't precise if the change was due to Daylight Savings Time or just a general need to change the time so I'm giving you the complete set of instructions for changing the time

Setting the Time
Note: Use 24 hour and 60 minute format when setting the time.
For example: 2:00 pm = 14:00.
1. To make changes to your clock’s time setting, you must first put the clock into Program mode. See
section Accessing Program Mode to Update Calendar, Time and DST for instructions. When the
display flashes P1 dAtE, press the ◄ left button until P2 CL oc appears, then press the ● right
button.
2. Press the ◄ left button to choose 12-hour AM/PM display or 24-hour military time, then press the ●
right button.
3. Press the ◄ left button until the correct hour appears, then press the ● right button.
4. Press the ◄ left button until the correct minutes appear, then press the ● right button.
5. When the display flashes the correct time, press the ● right button.
6. The display flashes P2 CL oc again. To make changes to the DST start date or DST end date, press
the ◄ left button one time until P3 BDLE appears in the display and continue to How to Set the
Begin Date for DST. Otherwise, press the ◄ left button several times until END appears, and select
the ● right button to save your updated settings.
How to Disable DST
To disable Daylight Saving Time, set the DST beginning date equal to the ending date (i.e., DST begin
date = 08-03-09; DST end date = 08-03-09).
See next for details regarding setting the DST begin date and end date. Typically, clocks operating in
Arizona, Hawaii and Puerto Rico do not observe DST, and disabling the DST function typically applies to
those areas only.
By default, and unless otherwise modified, DST takes effect on the first Sunday in April and ends on the
last Sunday in October (this was the United States standard through 2006).
How to Set the Begin Date for DST
Note that in 2007 the standard for DST changes to a different start date and end date. The steps below
correct the change in the new start date for DST. The new start date and end date need only be changed
once, since the clock’s rolling calendar function auto-calculates the start date and end date for DST every
year thereafter.
Note that if the begin date for DST has passed, in addition to modifying the begin date (and end date),
you will also need to modify the time.
1. When the display flashes P1 dAtE (see earlier section Accessing Program Mode to Update
Calendar, Time and DST for instruction), press the ◄ left button until P3 bdLt appears, then press
the ● right button.
2. Press the ◄ left button until the correct year appears, then press the ● right button.
3. Press the ◄ left button until the correct month appears, then press the ● right button.
4. Press the ◄ left button until the correct date appears, then press the ● right button.
5. When the display flashes the correct year, month and date, press the ● right button.
6. When the display flashes P3 bdLt, press the ◄ left button once to select next menu option, P4
EdLt, which provides a way to set the end date of the Daylight Saving period.
Setting Daylight Savings Time for model PIX-10/15/21
Page 4 of 4
How to Set the End Date for DST
Note that in 2007 the standard for DST changes to a different start date and end date. The steps below
correct the change in the new end date for DST. The new start date and end date need only be changed
once, since the clock’s rolling calendar function auto-calculates the start date and end date for DST every
year thereafter.
1. When the display flashes P4 EdLt (see conclusion of previous section How to Set the Begin Date
for DST for instruction), press the ● right button.
2. Press the ◄ left button until the correct year appears, then press the ● right button.
3. Press the ◄ left button until the correct month appears, then press the ● right button.
4. Press the ◄ left button until the correct date appears, then press the ● right button.
5. When the display flashes the correct year, month and date, press the ● right button.
6. The display flashes P4 EdLt again. Press the ◄ left button several times until END appears, and
select the ● right button to save your updated settings.
Note: To disable the Daylight Saving Setting, set the beginning and ending date to the same date.

Apr 16, 2009 | Office Equipment & Supplies

1 Answer

How to calculate the difference of two dates by year, month and day using the formula in excel?


To calculate the difference in Years use =Year(c,r)-Year(c,r)
To calculate the difference in Months use =Month(c,r)-Month(c,r)
To calculate the difference in Months use =(c,r)-(c,r)

c= column, r= row

You will need to format the source cells as dates

Mar 15, 2009 | Microsoft Office Excel 2003 for PC

1 Answer

What is the formula,if a cell having start date of some batch, and i f i need to calculate the end date after 15 month ?


Use this forumla =EDATE(A1,15)

example in cell A1 you enter 3/11/2009 and in cell A3 you enter=EDATE(A1,15)

You will first see a serial number representing the new date. You must format cell A3 to display the value as a date. it should be:
6/11/2010

Mar 12, 2009 | Microsoft Office Excel 2003 for PC

1 Answer

Calculation of end date in format dd/mm/yy


Assuming you put the start date in cell A1, put this formula:

=DATE(YEAR(A1),MONTH(A1)+15,DAY(A1))

in the cell where the result wanted to be appeared.

P.S.: If this information was helpful, please rate this solution.

Mar 12, 2009 | Microsoft Office Excel 2003 for PC

2 Answers

Need to change the time on calculator


To set up date, time, conversion rate, and tax/discount
rate, place the switch at the "RATE SET" position.
Date:
Enter in the order of month, day and year, then press date/time button
to complete the entry.
Use ▪ to separate month, day, and year EXAMPLE 3▪13▪2009.
When (date/time) button is pressed, the number entered is evaluated
and displayed as “date”
Time:
Enter in the order of hour, minutes, then press date/time button to
complete the entry.
(There is no entry available for the seconds value. The
clock starts at zero second.)
Use to separate hour and minute values EXAMPLE 8▪15
When is date/time button is pressed, the number entered is evaluated
and displayed as “time

When settings are complete place the switch back to GT

Mar 11, 2009 | Sharp EL-1197PIII Calculator

1 Answer

Converting date of a month to last working day of the month


use the below formula to get the date of the LWD (Monday thru Friday) of the month.
Assumed that if the date in cell A1 is 6-May-2005. then formula will be
=DATE(YEAR(A1),MONTH(A1)+1,0)-(MAX(0,WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,0),2)-5))

Jan 30, 2009 | Microsoft Excel for PC

3 Answers

Need excel solution


Hello ycool11, If your asking what i think you are the formula would be
=(a2-a1)*24
That assumes a2 is that last date entered and a1 is the initial starting date. if you would like a more intense formula let me know and i'm sure i can help.

Oct 22, 2008 | Microsoft Excel for PC

1 Answer

Creating a holiday accrual spreadsheet.


Hi, I guess you mean vacation accrual. It's hard to help you here, without knowing the exact accrual formula. However here are some tips: Set up a column with the start date for each employee and use the today() function to get today's date. You can use the days360() funtion to calculate the number of days between two dates, assuming regular 30 days/month. For more date related functions, click on the Fx (letter f with an subscript x) to the left of the value/formula entry field at the top of the spreadsheet. Thsi gives you a wizard with a list of functions. Select the Data/Time group and you see all available functions and can interactively pull them together. Good luck K

Sep 05, 2007 | Microsoft Office Standard for PC

Not finding what you are looking for?
Microsoft Office Excel 2003 for PC Logo

953 people viewed this question

Ask a Question

Usually answered in minutes!

Top Microsoft Computers & Internet Experts

micky dee

Level 3 Expert

2905 Answers

Piyal Perera
Piyal Perera

Level 3 Expert

528 Answers

Les Dickinson
Les Dickinson

Level 3 Expert

18410 Answers

Are you a Microsoft Computer and Internet Expert? Answer questions, earn points and help others

Answer questions

Manuals & User Guides

Loading...