How many actual days left formula

  • Thread starter Thread starter rob nobel
  • Start date Start date
R

rob nobel

Can someone please create a formula for me that will show the number of
weeks and days between 2 dates (as actual number of weeks and days - not a
fraction). The dates will always be within 12 months of each other.

Rob
 
Hi Rob!

With earliest date in A1 and later date in B1:
=INT((B1-A1)/7)&" weeks "&MOD(B1-A1,7)&" days"

If A1:
29-Jan-2004
And B1:
16-Feb-2004
Returns:
2 weeks 4 days
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
What about

=ROUNDDOWN(DATEDIF(A1,B1,"d")/7,0) & " weeks "
MOD(DATEDIF(A9,B9,"d"),7) & " days"

-Bo
 
Hi beeawwb!

You mean:
=ROUNDDOWN(DATEDIF(A1,B1,"d")/7,0) & " weeks "
&MOD(DATEDIF(A1,B1,"d"),7) & " days"

You'd got some Row 9 stuff.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
You're right. I was working in row 9 at the time, and when I pasted the
formula I forgot to change the other half.

Good spot. :)

-Bob
 
Hi Bob!

Bin there! Dun that!

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Thanks guys!
Both very helpful. Each of those formulas is a marked improvement over my
attempt. What I created was something akin to a long novel. (I was too
embarrassed to post it in the NG.)
Rob
 
Hi Rob!

Bin there, dun that too!!! But it's all part of the learning
experience and interesting to see different approaches. Much better
that you should struggle first rather than just say, "Help!" as soon
as you hit a problem.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Thanks for that bit of encouragement Norman.
As a result I will post the monstrosity I have created from one of the
formulas that were given me. It is my hope that this can be simplified
somewhat.
E5 to E38 is a column where dates are entered. When that column is full,
they are entered in L5 to L37.
I need the formula to find the latest date in those two columns and then use
that date to calculate the number of weeks and days between that and the
date in D5, (including both dates). The following works but I imagine it can
be done better!
I also cannot find any help on the DatedIf Function in the function section.
Why is that?
Rob

=IF(E5="","",IF(K5="",ROUNDDOWN(DATEDIF(D5-1,LOOKUP(G2,E5:E38),"d")/7,0)&"
weeks "
&MOD(DATEDIF(D5-1,LOOKUP(G2,E5:E38),"d"),7)&"
days",ROUNDDOWN(DATEDIF(D5-1,LOOKUP(G2,L5:L37),"d")/7,0)&" weeks "
&MOD(DATEDIF(D5-1,LOOKUP(G2,L5:L37),"d"),7)&" days"))
 
Hi Rob!

It's difficult without being able to test on your workbook but here's
what I get:

=IF(E5="","",ROUNDDOWN((LOOKUP(G2,IF(K5="",$E$5:$E$38,$L$5:$L$37))-(D5
-1)/7),0)&" weeks
"&MOD(LOOKUP(G2,IF(K5="",$E$5:$E$38,$L$5:$L$37))-(D5-1),7)&" days")

Your lookup vector varies according to K5 = or <> "" so I put the IF
function regarding K5 into the lookup vector argument.
Your use of DATEDIF isn't really necessary as End_Date-Start_Date
returns days.
I've made the references to the lookup vectors absolute. It's a habit
because these formulas are often copied down and you don't want the
vector to change.
I think that your D5 and K5 references might need to be made absolute
as well.
I added a couple of spaces to your labels for cosmetic purposes.

But it needs testing as changes in this way without the workbook are
bound to produce problems. If it doesn't, then the beers are on me! I
just hate changing and playing around with formulas without a test.

DATEDIF has been an Excel built in function since way back when but
only made an appearance in Help in Excel 2000. Probably the best help
page is:

http://www.cpearson.com/excel/datedif.htm

Generally on complex formula building, I'd use John Walkenbach's
Megaformula technique to build up, test and make more efficient the
various options.


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Thanks Norman. Nearly perfect! Just made one minor change (I just put a
bracket after ROUNDDOWN and before the /7). But don't worry about the beers,
I don't drink anyway.
I appreciate the comment about absolutes but this formula will only ever be
in one cell.
The final working formula is:

=IF(E5="","",ROUNDDOWN(((LOOKUP(G2,IF(K5="",E5:E38,L5:L37))-(D5
-1))/7),0)&" weeks " & MOD(LOOKUP(G2,IF(K5="",E5:E38,L5:L37))-(D5-1),7)&"
days")

Regarding the DatedIf. It never occurred to me to look for help in help as
all the other functions have the help "ready made". Thanks again for all
your help and advice. I really appreciate the comments as well as the
fixes!!
Rob
 
hi,
in excell worksheet i have already entered a value or a text, if
again re-enter the same value or text excell should warn me that thi
value or text already exist.
can you please help me to find out the same formula
 
Back
Top