Date function

  • Thread starter Thread starter tsony
  • Start date Start date
T

tsony

Is anybody knows how to deduct two dates and the result should be in number
of weeks and days (eg. 26 days should be 3 weeks and 5 days)?
 
=INT((A2-A1)/7)&" weeks "& ((A2-A1)/7-INT((A2-A1)/7))*7&" days"

Vaya con Dios,
Chuck, CABGx3
 
Dear David,

If we include the end date what formula we use for eg 01/01/08 to 21/01/08
number of days should be 21. if we deduct the two dates the result comes to
20, please advice
 
You are saying you want to add 1 to the result of the B2-A2 subtraction. Do
you need someone to tell you how to add 1 to B2-A2?
 
David,

You have mistaken. In excel if we subtract two dates, it wont include the
end date. I know how to add and subtract 1. Thanks for your reply.
 
David,

Sorry to bother you. One more question. This is related to the old one.
After finding the number of weeks there is another classification. if it is 0
- 3 weeks 25% of 100
4 - 6 weeks 50% of 100 7 - 10 weeks 75% of 100. Which formula we use to find
the result.
 
You have mistaken. In excel if we subtract two dates, it wont
include the end date.
Click to expand...

I'm not sure what mistake you think David made, but what you are talking
about is not something confined to Excel and dates. And, actually, to be
technical about it, it is the start date that is not included. The opposite
of subtraction is addition... the value you get by subtracting a first
number form a second number is what has to be added to the first number in
order to get back to the second one again. This applies to all numbers, not
just Excel dates. For example, using your day numbers from the same month...
21-1=20, not 21. As David alluded to, if you need to include the start date
in the count of the difference in your days, you have to add 1 to your
subtraction result.

Rick
 
The numbers you are using in your example do not make sense to me. First,
there are 52 weeks in a year, not 100, unless the 100 stands for something
else. Second, your week range percentages won't get anywhere near the 100
value you are using before hitting 100%. Third, your week ranges are
varying... there is one less week in the 4-6 weeks span than either of the
other two shown or what is left over for the implied 11-12 final weeks span.
Could you give more realistic numbers for what you are looking for?

Rick
 
Rick,

100 is not weeks. It is only a number. The week ranges are varying, it is
not fixed. Is that helps you.

Thanks
 
=LOOKUP(INT((B2-A2+1)/7),{0,4,7,11},{0.25,0.5,0.75,1})*100

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
I guess this will do what you want...

=IF(INT((B2-A2)/7)<4,0.25,IF(INT((B2-A2)/7)<7,0.5,IF(INT((B2-A2)/7)<11,0.75,IF(INT((B2-A2)/7)<14,1,NA()))))*100

Note, it produces a #N/A error if the number of weeks is 14 or greater. The
100 on the end is the number you specified; perhaps it would be better if
you put that value in a cell and put the cell reference in the formula
instead.

Rick
 
With start date in A2 and end date in B2 as suggested previously try

=MATCH((B2-A2+1)/7,{0,4,7,11})/4
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Formula to tell day of week 2
date function 10
Dates formula 5
Week count 2
General numbers lookup from FY-PP-WK-D resulting in MM-DD-YY 3
date intervals calculation 9
Time attendance calculation problem 1
Calculate End Date Using Start Date and No. of Days excluding specific days. 0

Back
Top