date spreadsheet

JGarza · Mar 27th, 2002, 04:10 PM

I am trying to calculate the number of weeks from one date to another (ie how many weeks are there between 3/03/01 thru 8/1/02)

Does anyone know a formula?

BabyTiger · Mar 27th, 2002, 04:17 PM

Hi,

You might want to try using the following formula:

=(B1-A1+1)/7

where B1 = end date, A1 = start date.

HTH

Joe Was · Mar 27th, 2002, 04:26 PM

This will break-out a full date:

=IF(F24="","Please Enter Your Date Above!",DATEDIF(F24,NOW(),"y")&" years, "&DATEDIF(F24,NOW(),"ym")&" months and "&DATEDIF(F24,NOW(),"md")&" days")

Note: You need the analyze addin that comes with Excel installed to work it.

The date data is in "F24" the formula can be in any cell. You can strip out the other parts if you need to. JSW

Joe Was · Mar 27th, 2002, 04:29 PM

And in the style of the above:

=IF(OR(F13="",F16=""),"Add Both Dates Above!",DATEDIF(F13,F16,"y")&" years, "&DATEDIF(F13,F16,"ym")&" months and "&DATEDIF(F13,F16,"md")&" days")

Will give the between age. JSW

JGarza · Mar 27th, 2002, 04:31 PM

What does this tell me?

end date - beginning date +1 divided by 7

What does the one signify???

BabyTiger · Mar 27th, 2002, 04:34 PM

Hi,

The + 1 means the formula will include the start date as well.

For example, start date = 1/4/02, and end date = 2/4/02. If just use end - start then the answer is 1, but in fact there are 2 dates in between.

That is the reason for + 1.

HTH

Dave Patton · Mar 27th, 2002, 08:35 PM

Attention Joe Was

Re Datedif You stated "Note: You need the analyze addin that comes with Excel installed to work it. "

Datedif is a regular function; however, it was only documented in Excel 2000

Dave Patton · Mar 27th, 2002, 08:42 PM

Unlike Datedif, VBA's Datediff has a Week parameter.

With VBA, I created a User Defined Function (UDF) named Weeks . Use it as follows
=Weeks(A4,B4)

The UDF is created in a regular VBA module; you can copy the following into the module.

Function Weeks(dFirstDate, dSecondDate) As Integer
Application.Volatile
Weeks = DateDiff("ww", dFirstDate, dSecondDate)
End Function

[ This Message was edited by: Dave Patton on 2002-03-28 11:07 ]

Yogi Anand · Mar 27th, 2002, 09:20 PM

Hi Dave:
Your UDF DateDiffW works -- beautiful!

JGarza · Mar 28th, 2002, 08:39 AM

Joe Was, Dave Patton & Yogi Anand

I AM SO CONFUSED??

What is a VBA and a UDF??

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 27th, 2002, 04:10 PM	#1
JGarza Board Regular Join Date: Mar 2002 Posts: 93	I am trying to calculate the number of weeks from one date to another (ie how many weeks are there between 3/03/01 thru 8/1/02) Does anyone know a formula?

Mar 27th, 2002, 04:17 PM	#2
BabyTiger Board Regular Join Date: Mar 2002 Location: Wellington Posts: 104	Hi, You might want to try using the following formula: =(B1-A1+1)/7 where B1 = end date, A1 = start date. HTH

Mar 27th, 2002, 04:26 PM	#3
Joe Was MrExcel MVP Join Date: Feb 2002 Location: Central Florida, USA Posts: 7,541	This will break-out a full date: =IF(F24="","Please Enter Your Date Above!",DATEDIF(F24,NOW(),"y")&" years, "&DATEDIF(F24,NOW(),"ym")&" months and "&DATEDIF(F24,NOW(),"md")&" days") Note: You need the analyze addin that comes with Excel installed to work it. The date data is in "F24" the formula can be in any cell. You can strip out the other parts if you need to. JSW

Mar 27th, 2002, 04:29 PM	#4
Joe Was MrExcel MVP Join Date: Feb 2002 Location: Central Florida, USA Posts: 7,541	And in the style of the above: =IF(OR(F13="",F16=""),"Add Both Dates Above!",DATEDIF(F13,F16,"y")&" years, "&DATEDIF(F13,F16,"ym")&" months and "&DATEDIF(F13,F16,"md")&" days") Will give the between age. JSW

Mar 27th, 2002, 04:31 PM	#5
JGarza Board Regular Join Date: Mar 2002 Posts: 93	What does this tell me? end date - beginning date +1 divided by 7 What does the one signify???

Mar 27th, 2002, 04:34 PM	#6
BabyTiger Board Regular Join Date: Mar 2002 Location: Wellington Posts: 104	Hi, The + 1 means the formula will include the start date as well. For example, start date = 1/4/02, and end date = 2/4/02. If just use end - start then the answer is 1, but in fact there are 2 dates in between. That is the reason for + 1. HTH

Mar 27th, 2002, 08:35 PM	#7
Dave Patton Board Regular Join Date: Feb 2002 Location: Calgary, Alberta Canada Posts: 2,065	Attention Joe Was Re Datedif You stated "Note: You need the analyze addin that comes with Excel installed to work it. " Datedif is a regular function; however, it was only documented in Excel 2000

Mar 27th, 2002, 08:42 PM	#8
Dave Patton Board Regular Join Date: Feb 2002 Location: Calgary, Alberta Canada Posts: 2,065	Unlike Datedif, VBA's Datediff has a Week parameter. With VBA, I created a User Defined Function (UDF) named Weeks . Use it as follows =Weeks(A4,B4) The UDF is created in a regular VBA module; you can copy the following into the module. Function Weeks(dFirstDate, dSecondDate) As Integer Application.Volatile Weeks = DateDiff("ww", dFirstDate, dSecondDate) End Function [ This Message was edited by: Dave Patton on 2002-03-28 11:07 ]

Mar 27th, 2002, 09:20 PM	#9
Yogi Anand MrExcel MVP Join Date: Mar 2002 Location: Michigan USA Posts: 11,452	Hi Dave: Your UDF DateDiffW works -- beautiful!

Mar 28th, 2002, 08:39 AM	#10
JGarza Board Regular Join Date: Mar 2002 Posts: 93	Joe Was, Dave Patton & Yogi Anand I AM SO CONFUSED?? What is a VBA and a UDF??