Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
converting days into weeks | Excel Worksheet Functions | |||
Calculate Number of Months Weeks and Days Between Two Dates | Excel Worksheet Functions | |||
return calculation every four weeks | Excel Discussion (Misc queries) | |||
How to calculate in weeks and days? | Excel Discussion (Misc queries) | |||
A number of days into weeks and days | Excel Worksheet Functions |