Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Create Calculation to return number of weeks and days

I need to create a formula where it calculates the number of weeks and days
between 2 dates.

For example:
A2 = 12/25/08
B2 = 1/30/09
C2 = 5 weeks 1 day

I found example formulas that calculates years, months and days....but no
weeks.

Any help is most appreciated!
Thanks!
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4,393
Default Create Calculation to return number of weeks and days

If for you "WEEK" is any 7 days then weeks = =INT((B2-A2)/7) and days =
=MOD((B2-A2),7)

But it gets complicated if WEEK is a 'calendar week'?
If so does your week begin on Sunday or Monday?
If we start on Tuesday of week 1 and end on Wednesday or week 2, how may
weeks will this be for you purpose? It is two partial weeks and no complete
weeks.

best wishes

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Lisa D" <Lisa wrote in message
...
I need to create a formula where it calculates the number of weeks and days
between 2 dates.

For example:
A2 = 12/25/08
B2 = 1/30/09
C2 = 5 weeks 1 day

I found example formulas that calculates years, months and days....but no
weeks.

Any help is most appreciated!
Thanks!



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,058
Default Create Calculation to return number of weeks and days

In C2 enter:
=ROUND((B2-A2)/7,0) & " weeks " & (B2-A2)-7*ROUND((B2-A2)/7,0) & " days"

--
Gary''s Student - gsnu200822


"Lisa D" wrote:

I need to create a formula where it calculates the number of weeks and days
between 2 dates.

For example:
A2 = 12/25/08
B2 = 1/30/09
C2 = 5 weeks 1 day

I found example formulas that calculates years, months and days....but no
weeks.

Any help is most appreciated!
Thanks!

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,501
Default Create Calculation to return number of weeks and days

Try

=INT(DATEDIF(A2,B2,"d")/7) & " Weeks "&MOD(DATEDIF(A2,B2,"d"),7)&" Days"

Mike

"Lisa D" wrote:

I need to create a formula where it calculates the number of weeks and days
between 2 dates.

For example:
A2 = 12/25/08
B2 = 1/30/09
C2 = 5 weeks 1 day

I found example formulas that calculates years, months and days....but no
weeks.

Any help is most appreciated!
Thanks!

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Create Calculation to return number of weeks and days

That did it!!!!! Thank you soooooo much!!!!!!

"Mike H" wrote:

Try

=INT(DATEDIF(A2,B2,"d")/7) & " Weeks "&MOD(DATEDIF(A2,B2,"d"),7)&" Days"

Mike

"Lisa D" wrote:

I need to create a formula where it calculates the number of weeks and days
between 2 dates.

For example:
A2 = 12/25/08
B2 = 1/30/09
C2 = 5 weeks 1 day

I found example formulas that calculates years, months and days....but no
weeks.

Any help is most appreciated!
Thanks!



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,651
Default Create Calculation to return number of weeks and days

With a 4 day gap, that returns 1 weeks -3 days.

Wouldn't it be better to change
=ROUND((B2-A2)/7,0) & " weeks " & (B2-A2)-7*ROUND((B2-A2)/7,0) & " days"
to
=INT((B2-A2)/7) & " weeks " & MOD(B2-A2,7) & " days" ?
--
David Biddulph

"Gary''s Student" wrote in message
...
In C2 enter:
=ROUND((B2-A2)/7,0) & " weeks " & (B2-A2)-7*ROUND((B2-A2)/7,0) & " days"

--
Gary''s Student - gsnu200822


"Lisa D" wrote:

I need to create a formula where it calculates the number of weeks and
days
between 2 dates.

For example:
A2 = 12/25/08
B2 = 1/30/09
C2 = 5 weeks 1 day

I found example formulas that calculates years, months and days....but no
weeks.

Any help is most appreciated!
Thanks!



  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,651
Default Create Calculation to return number of weeks and days

But of course DATEDIF(A2,B2,"d") is just B2-A2
--
David Biddulph

"Mike H" wrote in message
...
Try

=INT(DATEDIF(A2,B2,"d")/7) & " Weeks "&MOD(DATEDIF(A2,B2,"d"),7)&" Days"

Mike

"Lisa D" wrote:

I need to create a formula where it calculates the number of weeks and
days
between 2 dates.

For example:
A2 = 12/25/08
B2 = 1/30/09
C2 = 5 weeks 1 day

I found example formulas that calculates years, months and days....but no
weeks.

Any help is most appreciated!
Thanks!



  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 857
Default Create Calculation to return number of weeks and days

Hi,

Try

=INT((B2-A2)/7)&" weeks "&MOD(B2-A2,7)&" days"

If this helps, please click the Yes button

Cheers,
Shane Devenshire

"Lisa D" <Lisa wrote in message
...
I need to create a formula where it calculates the number of weeks and
days
between 2 dates.

For example:
A2 = 12/25/08
B2 = 1/30/09
C2 = 5 weeks 1 day

I found example formulas that calculates years, months and days....but no
weeks.

Any help is most appreciated!
Thanks!


  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,058
Default Create Calculation to return number of weeks and days

You are correct. My formula is badly flawed.
--
Gary''s Student - gsnu200822


"David Biddulph" wrote:

With a 4 day gap, that returns 1 weeks -3 days.

Wouldn't it be better to change
=ROUND((B2-A2)/7,0) & " weeks " & (B2-A2)-7*ROUND((B2-A2)/7,0) & " days"
to
=INT((B2-A2)/7) & " weeks " & MOD(B2-A2,7) & " days" ?
--
David Biddulph

"Gary''s Student" wrote in message
...
In C2 enter:
=ROUND((B2-A2)/7,0) & " weeks " & (B2-A2)-7*ROUND((B2-A2)/7,0) & " days"

--
Gary''s Student - gsnu200822


"Lisa D" wrote:

I need to create a formula where it calculates the number of weeks and
days
between 2 dates.

For example:
A2 = 12/25/08
B2 = 1/30/09
C2 = 5 weeks 1 day

I found example formulas that calculates years, months and days....but no
weeks.

Any help is most appreciated!
Thanks!




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
converting days into weeks chedd via OfficeKB.com Excel Worksheet Functions 5 April 22nd 23 12:13 PM
Calculate Number of Months Weeks and Days Between Two Dates [email protected] Excel Worksheet Functions 4 September 22nd 06 01:47 AM
return calculation every four weeks [email protected] Excel Discussion (Misc queries) 1 May 11th 06 12:15 PM
How to calculate in weeks and days? DORI Excel Discussion (Misc queries) 3 November 24th 05 02:50 AM
A number of days into weeks and days Robert Christie Excel Worksheet Functions 4 August 31st 05 03:23 AM


All times are GMT +1. The time now is 03:23 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"