Formatting a spreadsheet to calculate difference in dates etc.
 

Hi everyone I am new to this forum but require some help so what ever you can do will be much appreciated.

First I need to describe what I want to achieve:

I work in a company that supplies materials over a hire period, so the first column in my spreadsheet will be the items description (a2), followed by the Hire period (b2) which is the first problem I have come across as I cannot format the cell to be weeks. The next cell is the start date for hire (c2) followed by todays date (d2). The next cell is the difference between these two dates (e2) where i have used the formula: =+INT(DATEDIF(C2,D2,"D")/7)&" week(s) "&MOD(DATEDIF(C2,D2,"D"),7)&" day(s)"

I want the next cell to show me the how much E2 is over B2 which I would then multipy by an over hire charge rate (g2) giving me a value to charge (h2).

Any help in this matter will be much appreciated

Hi Craig,

Any chance you could post an example workbook with some dummy data?
It makes it far easier to help you...

S.

Formatting a spreadsheet to calculate difference in dates etc.
 

On Fri, 7 Sep 2012 08:23:24 +0000, Craig Padded Smith wrote:


Hi everyone I am new to this forum but require some help so what ever
you can do will be much appreciated.

First I need to describe what I want to achieve:

I work in a company that supplies materials over a hire period, so the
first column in my spreadsheet will be the items description (a2),
followed by the Hire period (b2) which is the first problem I have come
across as I cannot format the cell to be weeks. The next cell is the
start date for hire (c2) followed by todays date (d2). The next cell is
the difference between these two dates (e2) where i have used the
formula: =+INT(DATEDIF(C2,D2,"D")/7)&" week(s)
"&MOD(DATEDIF(C2,D2,"D"),7)&" day(s)"

I want the next cell to show me the how much E2 is over B2 which I would
then multipy by an over hire charge rate (g2) giving me a value to
charge (h2).

Any help in this matter will be much appreciated

I assume the value in B2 is a number representing the weeks alloted for the task. That being the case, it can be custom formatted as:

Format Cells/Number/Custom Type: 0" Week(s)"

E2: =INT((D2-C2)/7) & " Week(s) " & MOD(D2-C2,7) & " day(s)"
F2: =D2-C2-B2*7 -- "overage" in days. To get overage in weeks:
=(D2-C2-B2*7) / 7