Excel: take one date from another, display as weeks & days? 1

[andywing]

In Excel 2000

April 1, 2005 - March 10, 2005 = 22

I want this to display the 22 as 3 weeks 1 day(s)

I think that makes sense!

Is this easy?



Andy Wing
Local Government - Systems Administrator
England

 

[carrr]

See the "Calculating Age in VBA" example on this site:

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

Tired of waiting for an answer? Try asking better questions. See: faq222-2244

 

[SkipVought]

hi,

[tt]
a1 = 22
weeks: =int(A1/7)
Days: =mod(A1,7)
[/tt]


Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}

 

[mbarron]

Assuming A1 is greater of the two dates and B1 is the lesser:

=int((A1-B1)/7)&" weeks " &(A1-B1)- int((A1-B1)/7)*7&" day(s)"

Mike

 

[mbarron]

After seeing Skip's solution - The mof function would be much easier:

=TRUNC((A1-B1)/7)&" weeks " &MOD((A1-B1),7)&" day(s)"

Mike

 

[andywing]

Thank you Mike (mbarron) that works a treat, there's no way that I would have got there!

Brilliant!

Andy Wing
Local Government - Systems Administrator
England