Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
counting weeks and days between dates
I need a formula that will give me the total number of weeks and days
between 2 dates. ie. b3=Apr-2,2001, c3=Oct.31,2001 what is the total number of weeks and days between these dates. I have the formula to get the number of weeks but have trouble getting the number of days left to show. I have so far: =INT((C3-B3)/7) to get the weeks. When I count out the numbers, I get 3 calendar days left. But if I simply use the total number of days between B3 and C3 less number of weeks times 7 I get 2 days. It is out 1 day. Any ideas on how to make it more accurate? thx all Mel |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
counting weeks and days between dates
One way:
Weeks: "&INT((C3-B3)/7)&" Days: "&(C3-B3)-INT((C3-B3)/7)*7 Another way: ="Weeks: "&INT(DATEDIF(B3,C3,"d")/7)&" Days: "&DATEDIF(B3,C3,"d")- INT((C3-B3)/7)*7 wrote: I need a formula that will give me the total number of weeks and days between 2 dates. ie. b3=Apr-2,2001, c3=Oct.31,2001 what is the total number of weeks and days between these dates. I have the formula to get the number of weeks but have trouble getting the number of days left to show. I have so far: =INT((C3-B3)/7) to get the weeks. When I count out the numbers, I get 3 calendar days left. But if I simply use the total number of days between B3 and C3 less number of weeks times 7 I get 2 days. It is out 1 day. Any ideas on how to make it more accurate? thx all Mel |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
counting weeks and days between dates
Sorry. Left the =" off of the first way.
="Weeks: "&INT((C3-B3)/7)&" Days: "&(C3-B3)-INT((C3-B3)/7)*7 JW wrote: One way: Weeks: "&INT((C3-B3)/7)&" Days: "&(C3-B3)-INT((C3-B3)/7)*7 Another way: ="Weeks: "&INT(DATEDIF(B3,C3,"d")/7)&" Days: "&DATEDIF(B3,C3,"d")- INT((C3-B3)/7)*7 wrote: I need a formula that will give me the total number of weeks and days between 2 dates. ie. b3=Apr-2,2001, c3=Oct.31,2001 what is the total number of weeks and days between these dates. I have the formula to get the number of weeks but have trouble getting the number of days left to show. I have so far: =INT((C3-B3)/7) to get the weeks. When I count out the numbers, I get 3 calendar days left. But if I simply use the total number of days between B3 and C3 less number of weeks times 7 I get 2 days. It is out 1 day. Any ideas on how to make it more accurate? thx all Mel |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
counting weeks and days between dates
On Sep 26, 11:42 am, JW wrote:
Sorry. Left the =" off of the first way. ="Weeks: "&INT((C3-B3)/7)&" Days: "&(C3-B3)-INT((C3-B3)/7)*7 JW wrote: One way: Weeks: "&INT((C3-B3)/7)&" Days: "&(C3-B3)-INT((C3-B3)/7)*7 Another way: ="Weeks: "&INT(DATEDIF(B3,C3,"d")/7)&" Days: "&DATEDIF(B3,C3,"d")- INT((C3-B3)/7)*7 wrote: I need a formula that will give me the total number of weeks and days between 2 dates. ie. b3=Apr-2,2001, c3=Oct.31,2001 what is the total number of weeks and days between these dates. I have the formula to get the number of weeks but have trouble getting the number of days left to show. I have so far: =INT((C3-B3)/7) to get the weeks. When I count out the numbers, I get 3 calendar days left. But if I simply use the total number of days between B3 and C3 less number of weeks times 7 I get 2 days. It is out 1 day. Any ideas on how to make it more accurate? thx all Mel- Hide quoted text - - Show quoted text - works great. thx once again. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
counting weeks and days between dates
That is because when you subtract the start day from the end day, you don't
count the start day. you need to do your calculations with =INT((C3-B3+1)/7) =mod((c3-B3+1),7) that give me 30 weeks and 3 days. -- Regards, Tom Ogilvy " wrote: I need a formula that will give me the total number of weeks and days between 2 dates. ie. b3=Apr-2,2001, c3=Oct.31,2001 what is the total number of weeks and days between these dates. I have the formula to get the number of weeks but have trouble getting the number of days left to show. I have so far: =INT((C3-B3)/7) to get the weeks. When I count out the numbers, I get 3 calendar days left. But if I simply use the total number of days between B3 and C3 less number of weeks times 7 I get 2 days. It is out 1 day. Any ideas on how to make it more accurate? thx all Mel |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
counting weeks and days between dates
For your sample, both JW's suggestions gave me 30 weeks and 2 days - I
thought you said that was your problem - you wanted 3 days. Go figure??? -- Regards, Tom Ogilvy " wrote: On Sep 26, 11:42 am, JW wrote: Sorry. Left the =" off of the first way. ="Weeks: "&INT((C3-B3)/7)&" Days: "&(C3-B3)-INT((C3-B3)/7)*7 JW wrote: One way: Weeks: "&INT((C3-B3)/7)&" Days: "&(C3-B3)-INT((C3-B3)/7)*7 Another way: ="Weeks: "&INT(DATEDIF(B3,C3,"d")/7)&" Days: "&DATEDIF(B3,C3,"d")- INT((C3-B3)/7)*7 wrote: I need a formula that will give me the total number of weeks and days between 2 dates. ie. b3=Apr-2,2001, c3=Oct.31,2001 what is the total number of weeks and days between these dates. I have the formula to get the number of weeks but have trouble getting the number of days left to show. I have so far: =INT((C3-B3)/7) to get the weeks. When I count out the numbers, I get 3 calendar days left. But if I simply use the total number of days between B3 and C3 less number of weeks times 7 I get 2 days. It is out 1 day. Any ideas on how to make it more accurate? thx all Mel- Hide quoted text - - Show quoted text - works great. thx once again. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
counting weeks and days between dates
I know what you mean Tom. I was a little thrown off by his
description as well. I was just looking around and came across a page on Chip's site where he has a pretty slick method of doing this. His method also worked out to 30 weeks 2 days. =TRUNC((C3-B3)/7)&" Weeks "&MOD(C3-B3,7)&" Days" Tom Ogilvy wrote: For your sample, both JW's suggestions gave me 30 weeks and 2 days - I thought you said that was your problem - you wanted 3 days. Go figure??? -- Regards, Tom Ogilvy " wrote: On Sep 26, 11:42 am, JW wrote: Sorry. Left the =" off of the first way. ="Weeks: "&INT((C3-B3)/7)&" Days: "&(C3-B3)-INT((C3-B3)/7)*7 JW wrote: One way: Weeks: "&INT((C3-B3)/7)&" Days: "&(C3-B3)-INT((C3-B3)/7)*7 Another way: ="Weeks: "&INT(DATEDIF(B3,C3,"d")/7)&" Days: "&DATEDIF(B3,C3,"d")- INT((C3-B3)/7)*7 wrote: I need a formula that will give me the total number of weeks and days between 2 dates. ie. b3=Apr-2,2001, c3=Oct.31,2001 what is the total number of weeks and days between these dates. I have the formula to get the number of weeks but have trouble getting the number of days left to show. I have so far: =INT((C3-B3)/7) to get the weeks. When I count out the numbers, I get 3 calendar days left. But if I simply use the total number of days between B3 and C3 less number of weeks times 7 I get 2 days. It is out 1 day. Any ideas on how to make it more accurate? thx all Mel- Hide quoted text - - Show quoted text - works great. thx once again. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
counting weeks and days between dates
If your first date is the 1st of the month and your second date the 3rd,
that's 3 days if you count each of them but the difference between the dates is of course 2. If you want inclusive days, which it seems you do, add 1 to their difference. Consider two dates that fall on consecutive Mondays, for your purposes is that a week or eight days. Regards, Peter T "JW" wrote in message ups.com... I know what you mean Tom. I was a little thrown off by his description as well. I was just looking around and came across a page on Chip's site where he has a pretty slick method of doing this. His method also worked out to 30 weeks 2 days. =TRUNC((C3-B3)/7)&" Weeks "&MOD(C3-B3,7)&" Days" Tom Ogilvy wrote: For your sample, both JW's suggestions gave me 30 weeks and 2 days - I thought you said that was your problem - you wanted 3 days. Go figure??? -- Regards, Tom Ogilvy " wrote: On Sep 26, 11:42 am, JW wrote: Sorry. Left the =" off of the first way. ="Weeks: "&INT((C3-B3)/7)&" Days: "&(C3-B3)-INT((C3-B3)/7)*7 JW wrote: One way: Weeks: "&INT((C3-B3)/7)&" Days: "&(C3-B3)-INT((C3-B3)/7)*7 Another way: ="Weeks: "&INT(DATEDIF(B3,C3,"d")/7)&" Days: "&DATEDIF(B3,C3,"d")- INT((C3-B3)/7)*7 wrote: I need a formula that will give me the total number of weeks and days between 2 dates. ie. b3=Apr-2,2001, c3=Oct.31,2001 what is the total number of weeks and days between these dates. I have the formula to get the number of weeks but have trouble getting the number of days left to show. I have so far: =INT((C3-B3)/7) to get the weeks. When I count out the numbers, I get 3 calendar days left. But if I simply use the total number of days between B3 and C3 less number of weeks times 7 I get 2 days. It is out 1 day. Any ideas on how to make it more accurate? thx all Mel- Hide quoted text - - Show quoted text - works great. thx once again. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting days between dates | Excel Discussion (Misc queries) | |||
Counting days between 2 dates | Excel Discussion (Misc queries) | |||
Counting Days Between Dates | Excel Worksheet Functions | |||
Calculate Number of Months Weeks and Days Between Two Dates | Excel Worksheet Functions | |||
Counting days between dates | Excel Discussion (Misc queries) |