Calculating months in year July - June

[tuph]

This is probably a basic question, but how do use a date cell t
calculate how many months have gone by if my financial year runs fro
July to June and a sale was made in April (the result should be 10)?

Thanks in anticipation :confused

 

[duane]

if months are in cells a1 and a2

=if(month(a2)<month(a1), 12 + month(a2)-month(a1), month(a2)-month(a1)

 

[Bob Phillips]

=DATEDIF(FY_Start,sale_date,"m")+1

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[tuph]

Thanks, Bob, but I can't get your formula to work. I entered the
formula as:

=DATEDIF(1/7/2005,A10,"m")+1 (where the date in A10 = 1/10/2005)

and got the result of 1270, whereas the result I'm looking for is 4.

Should I have substituted something for "m"?

Trish

 

[David Biddulph]

Thanks, Bob, but I can't get your formula to work. I entered the
formula as:

=DATEDIF(1/7/2005,A10,"m")+1 (where the date in A10 = 1/10/2005)

and got the result of 1270, whereas the result I'm looking for is 4.

Should I have substituted something for "m"?

Yes "ym", but you also need to get the starting date into tyhe right format;

=DATEDIF(DATE(2005,7,1),A10,"ym")+1

 

[David Biddulph]

Yes "ym", but you also need to get the starting date into tyhe right
format;

=DATEDIF(DATE(2005,7,1),A10,"ym")+1

Too hurried reply again, I'm sorry. Actually the "m", rather than "ym" is
OK if the difference is less than a year. If there are years & months & you
need to sort out the extra months beyond the full years, that's where the
"ym" would be needed.

 

[Bob Phillips]

You have passed an invalid date, try

=DATEDIF(date(2005,7,1),A10,"m")+1

or

=DATEDIF(--"2005-07-01",A10,"m")+1

or better put it in a cell and reference that cell.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[tuph]

Bewdiful!!! Thanks very much, Bob. That's exactly what I needed.

Trish