Free Microsoft Excel 2013 Quick Reference

- Calculate difference between two dates in days, months and year
- Formula for difference between two dates in Y&M
- Difference between two dates in months with decimals
- Calculate the difference between two dates, keeping the cell blank until date entered
- Difference between two dates
- Difference between two dates in years, months and days.
- Calculating number of three month periods between two dates...
- Calculating Dates in Terms of Months
- Calculating number of three month periods between two dates...
- Dates vs Dates then use an If Formula??
- Calculate Day, Months & Years Between Dates
- How to auto increase a monthly range, & addition formula based on dates if criteria
- Problem with 'hours worked between' function
- ISBLANK is messing up my datedif calculation :( PLEASE HELP!
- Date calcs - updated-have BEEN GIVEN ANSWER = SORTED! ;)
- Better way of handling dates?
- Simplify a date formula -Datedif
- Datedif ?
- Compare data from two sheets in one workbook
- Help calculating dates

I have an excel worksheet that has two date columns.

I want to calculte the difference between two dates and express this in Years and Months (without days shown).

I can already calculate the total of days between the two dates but thats no good for me.

So, 31 March 2007 minus 31 January 2006 would need to equal "1 year 2 months".

Look forward to hearing from you.

with a monthly fixed fee. This means that the fee per day is different

depending on the month (or even year if it's February).

Example...

Assume the monthly fixed fee is 1000.

From 05 March (27 days to count) to 20 April (20 days to count)

the calculation would be: 1000 * (27/31 +20/30) = 1537,63

From 05 April (26 days to count) to 20 May (20 days to count):

1000 * (26/30 + 20/31) = 1511,83

I know it's possible to use something like

Datedif(data1;date2;"d")/30,4375 but this uses an average number of

days per month and doesn't produce an exact enough result.

The problem is to find the exact number of months WITH decimals to

multiply the fee with... (keeping in mind also that it can be more than

12 months).

Does anyone know a simple trick to solve this?

Thx

--

effem

------------------------------------------------------------------------

effem's Profile: http://www.excelforum.com/member.php...o&userid=16112

View this thread: http://www.excelforum.com/showthread...hreadid=275586

Currently I have:

=YEAR(S2)-YEAR(P4)-IF(OR(MONTH(S2)

Say Date 1 = 16/3/2000

Say Date 2 = 31/3/2005

Now I need to calculate the completed year (diff. between the dates) in one column and months in Other Column.

Any 1 days should be completed as complete month.

Say in above case Year will be 5 years and 15 days .

Now i want the 5 years in one column , completed months in other column.

i.e answer should be 5 years & 1 month.

Please do reply urgently

I want to calculate number of years, months and days between a hire date and

today's date.

today's date.

I've been working on a function to calculate how many quarters there

are between two dates - but not the standard business quarters -

effectively what I am trying to work out is the number of complete

three month periods between two dates.

Example:

Start Date: 27/02/2006

End Date: 19/04/2013

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 4, 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

Therefore, I am trying to figure out how many entire three month

periods has passed between the two dates.

I'm sure this is no specific function that will do, but can it be done

with a combination of other existing function - at the moment it looks

like I'm going to have to write a VBA macro to parse the dates using

lots of IF..ELSE statements.

Also, a quarter (for this purpose) is defined as the first day in the

period to the last day in the period.

Therefore, 1 Jan - 31 March is a complete quarter (for tax purposes),

not 1 Jan - 1 April.

I tried the obvious line:

=FLOOR((DATEDIF(STARTCELL,ENDCELL,"M")/3),1

Using the first set of dates, I get 0, and with the second set I get 1

It is close, but not what I actually am trying to do.

Any suggestions greatly appreciated, as I'm getting quite stumped on

this...

Thanks

Neil.

between two dates. I want the answer to be shown in months only, therefore I

would need any additional days to be converted to a decimal. I tried to the

datedif function, but it does not recognize that the time span between months

may cross over multiple years.

For example I have the following:

Start Date: 04/15/02

End Date: 06/28/03

The number of whole months in between is 14, however their are also

additional days in between and I am looking to convert those to a decimal.

I know the difference in months

I've been working on a function to calculate how many quarters there

are between two dates - but not the standard business quarters -

effectively what I am trying to work out is the number of complete

three month periods between two dates.

Example:

Start Date: 27/02/2006

End Date: 19/04/2013

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 4, 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

Therefore, I am trying to figure out how many entire three month

periods has passed between the two dates.

I'm sure this is no specific function that will do, but can it be done

with a combination of other existing function - at the moment it looks

like I'm going to have to write a VBA macro to parse the dates using

lots of IF..ELSE statements.

Also, a quarter (for this purpose) is defined as the first day in the

period to the last day in the period.

Therefore, 1 Jan - 31 March is a complete quarter (for tax purposes),

not 1 Jan - 1 April.

I tried the obvious line:

=FLOOR((DATEDIF(STARTCELL,ENDCELL,"M")/3),1

Using the first set of dates, I get 0, and with the second set I get 1

It is close, but not what I actually am trying to do.

Any suggestions greatly appreciated, as I'm getting quite stumped on

this...

Thanks

Neil.

Im a newbie at this - but I have trawled through the forums - but seem to come close to the answer without finding it. So here goes.

A1 - Has a date (Called Request Date) When I asked for a delivery

B1 - Has a date. (Called Date Promised) What the supplier can achieve.

What I'm trying to do - is work out if a supplier is shipping early - or late.

So once the formula has worked out the difference I need to know if it is later than requested or if it is early - i.e run an IF formula.

To calculate the difference I use.....

=A6-DATE(YEAR(A6),MONTH(A6)-IF(DAY(A6)

I have two columns with dates (and times) in that I am trying to define how many days, hrs and mins have elapsed i.e. A1 has 12/12/06 21:00, B1 has 17/12/06 21:00. C1 has B1-A1 and is custom formatted to show as dd"days" hh"hrs" mm"mins". In this case it will therefore show as 5days 0hrs 0mins. Which is correct.

However, if more than 1month has elapsed then the format m"m" d"days" h"hrs" m"mins" does not work. For example 17/03/06 03:00 to 20/12/06 07:00 shows as 10m 4days 4hrs 00min, which it clearly isn't.

I know the reason it does this is because it calculates the difference between the two times and adds that to it's 0 value, which in my format is 01/01/1900 00:00. therefore when it adds 277days (the answer) it becomes 04/10/1900 04:00, so my formatting is just calling the month value ('10') and the day value ('4').

I understand the reason it does this, 277 days on from 01/01/1900 is indeed Oct 4th, but 277 days on from 17/03/06 is not 10months and 4 days as there are different length months in between. It also seems to add a month on, possibly because the format for 'months' is between 1 & 12 and therefore cannot begin at 0?

Does anyone know if it's possible to force excel to work out the correct number of months and days have elapsed between two dates and not apply it to 01/01/1900? Or any other possible solution, maybe with a different custom format?

Thanks in advance for any help/suggestions.

KJ

I am hoping for help with two date formula queries I have.

I have built a spreadsheet which consultants at my work (a recruitment agency) list their successful job placements on. We record the placements monthly, and each month I simply copy and paste the previous month's 'template' (a 'block' of 60 rows) down into the area immediately below. The 'template' then has all the right formulae in the right place, one of which is a formula to auto increase the month by one. So if the previous month was March 2009, the next pasted 'block/template' will automatically be entitled April 2009. The placements are then summed up automatically per consultant per month with further formulae in the template that make calculations based on the data in the month's 'block'.

Some recent changes to the info we want to see per consultant per month leads me into my 1st question:

Instead of just listing March 2009, I now need (I think, unless Excel can be a bit clever) the date to be essentially the range of the whole month, ie 01/03/2009-31/03/2009 (I am in the UK), or for months with fewer days to cover the relevant period ie 01/02/2009-28/02/2009. Is there a way to do this? And let me explain why I think I need it in my 2nd question (the resolution to question 2 may mean my question 1 is not the best way of going about things).

I have a formula that totals the placement fees per consultant by using a sumproduct. The formula (in cell AN154) basically adds up a fee if the consultant's initials by the fee match the consultant's initials where I want the calculation to take place (cell A135). I now need to have a further if criteria, and the formula is getting a bit complicated, so I would appreciate some advice. The formula is as follows:

=SUMPRODUCT((I136:I189=AD154)*(K136:K189))

where the consultant's initials for the calculation are in cell AD154 and column I is where a consultant puts their initials in the data section. Column K is the placement fee.

I now need to modify the formula so that it checks the date an invoice will be raised for a placement for a certain month (ie I need to know the total value of invoices that will be raised in February for a specified consultant - placements are listed when they are made but we only invoice on the start date of a candidate and due to the nature of jobs a placement could be confirmed one month but the candidate may not start till the next month or even the month after).

So this is why I asked the first question. My modified calculation formula needs to check if the placement will be invoiced in the month range of the 'block' which will be copied & pasted down for the next month and so on. So it needs to sum any fee which matches the relevant initials, if the invoice date is with the date range of the month block that this formula is in. (eg if we are looking at the block for March 2009, for placements made by the consultant with initials MG, the calculaton needs to check the entire spreadsheet for fees associated with MG that will be invoiced in March 2009).

Column S is where consultants list the date for their placement to be invoiced. Column I is where they list their initials. Column K is where they list the fee value. My modified formula will have to look at the total columns rather than just the 60 rows in each 'block' because, as mentioned earlier, placements made one month can be invoiced in later months. Can someone help me with the modification of the formula? And also how to create the self calculating month formula which heads up each 'block' (I currently use

=DATE(YEAR(A67), MONTH(A67)+1, DAY(A67)) where A67 is the previous month in the previous 'block' in the format 01/03/2009.

If it can't be done in one cell, I am happy to use my existing formula on two separate cells as long as the calculation formula can check a date range between a date in one cell and a date in another cell - and as long as the different number of days in each month can be handled by excel somehow? Ie having 01/01/2009 in cell A1 and 31/01/2009 in cell B1, using my above formula would produce 01/02/2009 in cell A2 but 03/03/2009 in B2 - I need it to be 28/02/2009.

sorry for the length of my post, it's hard to explain concisely!

In fact, I have just realised I can upload my file. Cell A135 is where I would like to enter the date range by formula.

Cell AN154 is where I would like to modify my formula to check if the invoice date (ie check the whole of column S) is within the month of cell A135.

That way I can simply copy the block for a new month and the whole spreadsheet is self calculating, apart from cell A67 which is where I need to enter the starting point for the dates manually.

Can anyone tell me what formula I need to put in AN154 and how to do the auto month increase problem? Thanks in advance.

I've scoured your site and taken all the advise I can on how to calculate the time difference between two date/time fields, taking in to consideration a standard days working hours and excluding weekends.

My formula appears to be working for some cases and not for others

Here is the function

[posted again for easier viewing]

=IF(MONTH(G29) & "/" & DAY(G29) & "/" & YEAR(G29)=MONTH(H29) & "/" & DAY(H29) & "/" & YEAR(H29),(NETWORKDAYS(G29,H29,N$1)-1)*(M$1-L$1)+MEDIAN(MOD(H29,1),L$1,M$1)-MEDIAN(MOD(G29,1),L$1,M$1),(NETWORKDAYS(G29,H29,N$1))*(M$1-L$1)+MEDIAN(MOD(H29,1),L$1,M$1)-MEDIAN(MOD(G29,1),L$1,M$1))

where:

Column G contains "Start Time" [dd/mm/yy hh:mm]

Column H contains "End Time" [dd/mm/yy hh:mm]

Column I contains the hours between using the "[h]:mm" formatted cell

Cell L1 contains start time of shift "7:30"

Cell M1 contains end time of shift "16:36"

Cell N contains is blank as I haven't then need for holidays

I noticed that when the days fell on the same day I was getting an additional unit added to the result so hence the {if both fall on same day then -1} (This appears to work but please correct me if I have made an error with this)

The problem appears to only when passing over multiple days, but I've seen other solved threads that use the same formula.

Here are some examples that appear to be working:

5/08/2011 9:20 5/08/2011 9:40 0:20

3/08/2011 11:47 3/08/2011 12:58 1:11

8/07/2011 10:14 8/07/2011 15:25 5:11

Here are some examples that are incorrect:

24/08/2011 15:00 25/08/2011 10:30 13:42

20/07/2011 15:00 21/07/2011 8:48 12:00

If anyone can shed any light on where I am going wrong, your help would be greatly appreciated.

I've attached a sample spreadsheet if that helps

Many thanks

Geoff

Cell C2: Entered into inventory date, D2 is the date it was taken out of inventory and J2 is my target cell.

My "DATEDIF" formula that works is: =DATEDIF(C2,D2,"y") & " years, " & DATEDIF(C2,D2,"ym") & " months, " & DATEDIF(C2,D2,"md") & " days"&IF(ISBLANK(D2),"",)

When I add the "ISBLANK" function to it, like this: =IF(ISBLANK(D2),"",(D2-C2))&DATEDIF(C2,D2,"y") & " years, " & DATEDIF(C2,D2,"ym") & " months, " & DATEDIF(C2,D2,"md") & " days"&IF(ISBLANK(D2),"",)

It leaves the it blank like it's suppose to but the years calculate like it's not reading the / between the month and year in the dates so I end up with something like 480 years 1 month 2days.

Please help, this is driving me crazy, I've worked on it for hours! Thank you!

e.g. 19/08/1998 and 01/03/07 (19 aug 98 and 1 march 2007)

I need the answer to appear as yr.mth (and month might need to be 02 rather than 20). e.g. 8.02 rather than 8.20

I think access used to be able to work something like this out, but i'm darned if i can get excel to.

I tried it for one set of dates and on the 4 different MS help options i tried they were all out by at least 2+ months.

So is this possible? The closest i got was calculating in days and then multiplying by 365.25 but the data was out 8.11 rather than 8.2 (actual answer) so thats 9 months out.

Anyone who has had a business and worked out numbers of years and months an employees has worked there (or tried to work out who is coming up for employment), maybe a techie teacher has found a formulae for comparing a child's chronological age against their test result age (to find the difference in months and years rather than have to manually re-calculate it every 2 months. (there's two of us who need help both with different jobs). Maybe someone has worked this formulae out to show how old people are in their contact address book...

Willing to try any other possible suggestions (tried all the ones that i understood the title of in the MS excel 2003 search page)

many thanks to anyone who helps us out!

(did a search on here and the archive page didn't show what i needed - had already tried the suggestion, and edited it 2 different ways to see if it worked).

have also tried: NONE OF THESE have worked

=(DATEDIF(D23,E23,"m"))/12 - didn't work

=YearFrac(B1,A1)*12 - didn't work

=DATEDIF(A1,A2,"Y") & "Year-" & DATEDIF(A1,A2,"YM") & "-Month-" & DATEDIF(A1,A2,"MD") & "-Days" - didn't work

=DATEDIF(D24,E24,"y") & " y " & DATEDIF(D24,E34,"ym") & " m " & DATEDIF(D24,E24,"md")&"d" - didn't work

=(E15-D15)/365.25

=(YEAR(E17)-YEAR(D17)*12+MONTH(E17)-MONTH(D17))

f18> =(YEAR(E18)-YEAR(D18)) )

g18> =(MONTH(E18)-MONTH(D18)) )

h18> =F18+(G18/100) ) combination got 8.20

rather than 8.2

=(YEAR(E17)-YEAR(D17)*12+MONTH(E17)-MONTH(D17))

Thank you for reading this, maybe someone can help me get closer to my answer (even if i need two columns until excel come up with a way to put it into 1 column, as later on i want to calcute the differences between 2 sets of data! (not asking for much here - am i?)

My function is below. What is the best way to pass to serial dates to a function and have the yearfrac function (or any date function) work with those two dates?

Do I really need to take each date and use the DATE function? My function should calculate the time in years between two dates and then perform an operation using that difference and then return the results.

Thanks,

Jeff

Function dr_eq20(Kr, Tm As Date, T1 As Date) As Double

Dim T As Double

Dim Month_m, Day_m, Year_m, Month_1, Day_1, Year_1 As Integer

Dim Yearfrac As Excel.Application

Month_m = Month(Tm)

Day_m = Day(Tm)

Year_m = Year(Tm)

Month_1 = Month(T1)

Day_1 = Day(T1)

Year_1 = Year(T1)

T=Yearfrac(Date(year_1, month_1, day_1),Date(year_m, month_m, day_m),1)

'T = Yearfrac(T1, Tm, 1)

dr_eq20 = (1 - Exp(-Kr * T)) / Kr

End Function

I would appreciate help to simplify this formula to calculate the number of months between two dates, but allowing for the differnt number of days in each month. My formula works, but there must be a neater way. I need some thinking from a different approach, I think.

=DATEDIF($B$19,IF(AND(MONTH(C19)MONTH(C19+1),DAY(C19)

This is more of a query on date dif

I was trying to calculate the difference between two dates on excel

I had a look through the search message board and searched the web generally where I came across the datedif function

I had found that in order to get the number of years it was

=datedif(startdate,enddate,"Y")

for months same formula"YM" and for days use "MD" in place of "Y" in the original formula

My question is when using datedif to calculate the number of days between two days it initialy gave me the right answer

Number of days between 01/08/2008 - 22/08/2008 - Answer 21 days

Using date dif gave me 21 days but when i did

01/08/2008 - 01/09/2008 - this gave me 0

Am I entering the formula wrong or does datedif simply not pick the difference if the days havent changed even though the month has ?

I then found i could simply minus one from the other , which then had me asking what is the point of date dif

I have a workbook with three sheets. "Comparison", "Last Year" & "This Year".

Each sheet will only have one calendar month of data in it (ie 1st Aug through to 31st Aug, 1 row per day)I would post the sheet here to view however I am new here and cant seem to find a link to attach a sheet.

I would like to import "Last Year" & "This Year" into "Comparison". The problem I am having is that I would like to import the 1st day of month of 2008 to "Comparison". Say for arguments sake that this day is a Friday for instance, I would like to import the first friday of "Last Year" and import it into "Comparison" into the cell above the imported "This Year" row. In the row below these two rows, I would like a row calculating the difference between the two days of data.

I would like this repeated so that "Comparison" is comparing each day from there on in for the whole months' data.ie, "Comparison" rows should be something like...

DateDayPULPULPDieselTotal Fuel03-Aug-07Friday1,381 7,746 4,799 13,926 01-Aug-08Friday2,834 13,662 13,191 29,687 Difference1,453 5,916 8,392 15,761 04-Aug-07Saturday943 5,883 2,767 9,593 02-Aug-08Saturday1,288 8,094 8,588 17,970 Difference345 2,211 5,821 8,377 05-Aug-07Sunday1,497 10,880 11,969 24,346 03-Aug-08Sunday780 6,022 5,143 11,945 Difference- 717 - 4,858 - 6,826 - 12,401

I want this to automatically import so that I can change the month to September and send out to the sites and it automatically compares the right days for whatever month I put in.

Thanks heaps again!

Matt

C9 = 12/31/2009

C10 = 1/31/2009

This being after 1/31/2010 there is 1 full year between today and C10 and 0 full years between today and C9. Additionally, this being 4/7/2010 there are 15 months between today and C10 and 3 full months between TODAY() and C9.

I am using the following:

which returns "3"

and

which returns "0"

OK... so everthing's good so far... HOWEVER:returns 14 instead of 15;

and

returns "0" instead of "1"

I've switched TODAY() with NOW() and vice versa with no change.

The B9 and B10 values are start dates of a time period (B9=1/1/2009, B10=3/1/2007). I've only included them in my equation so that I could fill my equation down and rows that have no start and end dates were blank instead of showing an error. Obviously rows with a start date but no end date return a "NO END DATE" message.

I'm pretty new at this, so if anyone can help me out that'd be great! FYI, the purpose of this is for compounding interest based upon several different calculations. I only want FULL months and FULL years to be considered.

Thanks

No luck finding an answer? You could always try Google.