Formatting a spreadsheet to calculate difference in dates etc.

[Craig Padded Smith]

Hi everyone I am new to this forum but require some help so what eve
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 th
first column in my spreadsheet will be the items description (a2)
followed by the Hire period (b2) which is the first problem I have com
across as I cannot format the cell to be weeks. The next cell is th
start date for hire (c2) followed by todays date (d2). The next cell i
the difference between these two dates (e2) where i have used th
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 woul
then multipy by an over hire charge rate (g2) giving me a value t
charge (h2).

Any help in this matter will be much appreciate

 

[Spencer101]

Hi everyone I am new to this forum but require some help so what eve
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 th
first column in my spreadsheet will be the items description (a2)
followed by the Hire period (b2) which is the first problem I have com
across as I cannot format the cell to be weeks. The next cell is th
start date for hire (c2) followed by todays date (d2). The next cell i
the difference between these two dates (e2) where i have used th
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 woul
then multipy by an over hire charge rate (g2) giving me a value t
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

 

[Ron Rosenfeld]

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