Excel Date Calcs

[bsack1369]

Here's the situation: I've created a spreadsheet in Excel 2010 (Win 7) where
I import daily transactions from our POS software. These transactions have
dates associated with them and I've calculated the time-delta between "First
Import" vs. "Last Import".

Here's what I've got so far...
First Import: 08/11/2009: Tue → Cell Name: LdgrImp_First
Last Import: 06/22/2011: Wed → Cell Name: LdgrImp_Last
Days in Range: 680 days → Cell Name: LdgrImp_RangeTotal

I'd like to break the above "Days in Range" of my calculated 680 days into
the following:
Years: _____ yrs → Cell Name: LdgrImp_Year
Months: _____ mos → Cell Name: LdgrImp_Month
Days: _____ days → Cell Name: LdgrImp_Days

My attempts at the code:
Years: "=ROUNDDOWN((LdgrImp_RangeTotal/365.25),0)" = 1
Months: "=ROUNDDOWN(((LdgrImp_RangeTotal-(LdgrImp_Year*365.25))/30.4375),0)" = 10
Days: "=ROUNDUP((LdgrImp_RangeTotal-(LdgrImp_Year*365.25)-(LdgrImp_Month*30.4375)),0)" = 11

Additional Notes:
1. To calc QTY years I've divided by 365.25 for Leap Year adjustment
→ 365 days * 3 years | plus a Leap Year | divided by 4 years
→ (((365*3)+366)/4) = 365.25 days/year

2. To calc QTY months I've divided by 30.4375 for Leap Year adjustment
→ Calculated average QTY days/yr | divided by 12 months
→ (365.25/12) = 30.4375 days/month

2. To calc QTY days I've subtracted the calculated "Years" and "Months"
from the "Days in Range"
and rounded UP to a whole number
→ (680-(1*365.25)-(10*30.4375)) = 11 days left over

My dilemma is that even though I'm conscious of taking into account of Leap
Year, my month QTY will still be represented by an "Average Days/Month"
instead of actual. This miscalculation will daisy-chain an error into my
calculated "Day" QTY.

I'm betting there's a *MUCH* easier way to do this???

 

[kpark91]

Hi, bsack,

I am guessing you want the exact year,month,day difference.
However, it is hard to justify the year, month, day difference because there can be 4 different answers from the same number of days as there is a set of 4 numbers which can combine to create a month. (28, 29, 30, 31)

As the number of days can differ for each month also depending on the leaf year, I believe there is no certain difference that everyone can agree to if the difference between two dates is in year, month, day format.

From seeing your calculation, I believe that is the best/easiest method you can do using formulas.

This was only my opinion :P
but if specific requirements are given and specific examples with answers are given with them, I think I can create a VBA code for you to run if you needed.

 

[abmati]

By adding few columns, you can find Years and Months from the given dates:
Regarding days a separate calculation required, will give later.

Excel Workbook
	A	B	C	D	E
1	08-Nov-09	01-Dec-09	1.494253	1	Year
2	22-Jun-11	31-May-11	0.494253	6	Month
Sheet1

Regards,

 

[bsack1369]

After reviewing both responses, I believe that VBA is going to have to do the trick.

Abmati: I've taken your example and tested some dates. If I assign A1: 01/01/2010 and assign A2: 01/01/2011, I should logically get 1 yrs, 0 mos. Instead I get 0 yrs, 11 mos. Even if I played with =DAY() function, I can get it to display that there should be an additional X# of days, but it still wouldn't say 2 yrs, 0 mos, 0 days; instead it calculates 1 yrs, 11 mos, 31 days.

I do appreciate your collaboration; I cannot tell you how nice it would be to collaborate with someone "in-house" that was at my level or higher with these things. This is the next best thing.

Thanks
bsack1369

 

[barry houdini]

Have you tried DATEDIF function? As with many date functions the results may be subjective because of variable month lengths etc. but it's simple....., e.g.

for years between start date in A2 and end date in B2

=DATEDIF(A2,B2,"y")

and then for months

=DATEDIF(A2,B2,"ym")

....and for days

=DATEDIF(A2,B2,"md")

DATEDIF isn't well documented in Excel - Chip Pearson explains here

 

[bsack1369]

Awesome Barry!

I was currently working with DATEDIF but it's not documented in Excel 2010's help (online or otherwise), just as you'd mentioned. But the Chip Pearson link gave me some Visine and a point in the right direction.

Much appreciated,
bsack1369

 

[abmati]

Please try this

Excel Workbook
	A	B	C	D
1	08-Nov-09	Days	Months	Years
2	22-Jun-11	14	7	1
Sheet1

 

[bsack1369]

I just wanted to extend my thanks to you all. The last two answers gave me the exact same answers... Always another way to skin a cat.

Shalom!
- Keepin' it Kosher since 1971