Formula for months

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello everyone
A1 = 01/01/04 00:00:0
B1 = 04/01/04 00:00:0
C1 = B1-A1
How do I modify my formula to return 3?
Thanks!
 
Hi Becky!

Use:
=DATEDIF(A1,B1,"m")

See Chip Pearson for the details of the mysterious DATEDIF function:
http://www.cpearson.com/excel/datedif.htm

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Hi Becky
there is no such magical number :-)
Therefor instead of calculating the day difference in C1 and using this
value as basis for the month calcualtion directly use the DATEDIF
function on your base values to return the month difference
 
OK...The entries are either daily, weekly, monthly, or yearly. If I add a cell D1 that says what the period is,(daily weekly monthly or yearly) what would I use to determine the months duration of the monthly entries? Or can I
Thanks!
 
Hi Becky!

If your data is as described D1 is daily, weekly, monthly, or yearly:

=IF(D1="weekly",INT((B1-A1)/7),DATEDIF(A1,B1,LEFT(D1,1)))

This gives the completed number of your chosen time period between A1
and B1. If you want decimal portions as well then post back but the
formulas are bit more difficult especially for months and years where
you don't have a constant.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
Becky said:
OK...The entries are either daily, weekly, monthly, or yearly. If I
Click to expand...
add a cell D1 that says what the period is,(daily weekly monthly or
yearly) what would I use to determine the months duration of the
monthly entries? Or can I?
 
Hi Becky!

Always pleased to explain formulas; we're not just into providing
solutions.

Re:
=IF(D1="weekly",INT((B1-A1)/7),DATEDIF(A1,B1,LEFT(D1,1)))

DATEDIF has three arguments.

Start date
End date
Unit

The units allowed include d for days, m for months and y for years.
But there isn't a w for weeks option.

In D1 you have one of the four entries daily, weekly, monthly, or
yearly

Weekly, I handle as the first case. The rest I handle using DATEDIF
and extracting the first letter of the word in D1 which happen to
represent the right unit letters for the period.

If you have a whole series of these to work out, you will need to use
careful absolute and relative cell referencing. So if you have 2
columns of pairs of dates and you want the differences using a
constant period in D1 you'd use:

=IF($D$1="weekly",INT((B1-A1)/7),DATEDIF(A1,B1,LEFT($D$1,1)))

If you want user flexibility, you might use a drop down in D1.

Data > Validation > List [provide a range with the four options or
type them in separated by commas].


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
-----Original Message-----
Hello everyone!
A1 = 01/01/04 00:00:00
B1 = 04/01/04 00:00:00
C1 = B1-A1
How do I modify my formula to return 3?
Thanks!
.
Click to expand...

format the numbers in a1 and b1 as zip codes, then
use '01/01/04 in a1 and '04/01/04 in b1 and it will return
4
 
Hi Chris!

I have the French equivalent as DATEDIF.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
~× said:
*Thanks for the help! I think I oversimplified the question. If C1 i
days I divide by 24, weeks divide by 168, years by 8760, whats th
magic number for months, in that some are 30 days, some 31 and a
occasional 28 and 29.
Sorry for the confusion
Becky *
Click to expand...

Hey Becky,

Why don't you try =month(a2-a1). But you need to make sure your cell
a2 and a1 are in date formats.

Monic
 
Hi Monique!

And if the dates are in different years?

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top