Date Formula
 

We have a client that has start dates in one colums and finish dates in
another column.
They need to produce information that shows the difference in weeks and days.
Is it possible for this to be done so that the weeks are shown in one column
and days are shown in another column.
If so a formula would be appreciated.
Also they have been inserting current information for the last year in
number format eg 12.12.06.
Is it possible to simply convert all of this to dates so that they can find
the difference or do they have to manually input all information again as
dates.

Thanks

JK

Date Formula
 

Hi

Perhaps:

With date1 in A1 and Date2 in B1 then in C1 (no of weeks):

=INT((B1-A1)/7)

and in D1 (no of Days)

=MOD((B1-A13)/7,1)*7

To convert the date they've entered (in text format) they can probably
use a sub formula:

=SUBSTITUTE(A1,".","/")+0

Hope this helps!

Richard

On 29 Jan, 08:50, ocdsupport
wrote:
We have a client that has start dates in one colums and finish dates in
another column.
They need to produce information that shows the difference in weeks and days.
Is it possible for this to be done so that the weeks are shown in one column
and days are shown in another column.
If so a formula would be appreciated.
Also they have been inserting current information for the last year in
number format eg 12.12.06.
Is it possible to simply convert all of this to dates so that they can find
the difference or do they have to manually input all information again as
dates.

Thanks

JK

Date Formula
 

Obviously it's better if the dates are input as recognisable dates, then if
your start date is in A2 and end date in B2 use this formula for the weeks

=INT((B2-A2)/7)

and for the days

=MOD(B2-A2,7)

You can convert a column of "dates" in the format mm.dd.yy to true dates by
using

Data Text to columns Next Next choose "date" and MDY option Finish

If you don't want to or can't convert from 12.12.2006 etc. you could use
these formulas for weeks and days

=INT((SUBSTITUTE(B2,".","/")-SUBSTITUTE(A2,".","/"))/7)

and

=MOD(SUBSTITUTE(B2,".","/")-SUBSTITUTE(A2,".","/"),7)


"ocdsupport" wrote:

We have a client that has start dates in one colums and finish dates in
another column.
They need to produce information that shows the difference in weeks and days.
Is it possible for this to be done so that the weeks are shown in one column
and days are shown in another column.
If so a formula would be appreciated.
Also they have been inserting current information for the last year in
number format eg 12.12.06.
Is it possible to simply convert all of this to dates so that they can find
the difference or do they have to manually input all information again as
dates.

Thanks

JK

Date Formula
 

thanks for replies. i wiii give them a try

jk

"daddylonglegs" wrote:

Obviously it's better if the dates are input as recognisable dates, then if
your start date is in A2 and end date in B2 use this formula for the weeks

=INT((B2-A2)/7)

and for the days

=MOD(B2-A2,7)

You can convert a column of "dates" in the format mm.dd.yy to true dates by
using

Data Text to columns Next Next choose "date" and MDY option Finish

If you don't want to or can't convert from 12.12.2006 etc. you could use
these formulas for weeks and days

=INT((SUBSTITUTE(B2,".","/")-SUBSTITUTE(A2,".","/"))/7)

and

=MOD(SUBSTITUTE(B2,".","/")-SUBSTITUTE(A2,".","/"),7)


"ocdsupport" wrote:

We have a client that has start dates in one colums and finish dates in
another column.
They need to produce information that shows the difference in weeks and days.
Is it possible for this to be done so that the weeks are shown in one column
and days are shown in another column.
If so a formula would be appreciated.
Also they have been inserting current information for the last year in
number format eg 12.12.06.
Is it possible to simply convert all of this to dates so that they can find
the difference or do they have to manually input all information again as
dates.

Thanks

JK