Hiya,

The spreadsheet I need function help on is to be used to flag end dates for
plant hire. We need to know when a piece of plant is over 2 weeks on hire to
keep track of where stuff is and what is available. I have used the following:
=IF(ISBLANK(B2),TODAY()-A2,"") this gives number of days from start of hire
and we can put in conditional formatting to flag up after X no. of days.

where b2 = booked until date
and a2 = start of hire

Problem is that there is another field that indicates actual hire end date
and I also need to use this so that if c2 = actual end of hire then the
formula cals no of days actual hire.

Hope this makes sense!

Many thanks
--
Pandora

What is the difference between 'booked until date' and 'actual end of hire
date'?

And what do you want to know about them?

Or is simply

=IF(AND(ISBLANK(B2),ISBLANK(C2)),TODAY()-A2,"")

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Pandora" wrote in message
...
Hiya,

The spreadsheet I need function help on is to be used to flag end dates
for
plant hire. We need to know when a piece of plant is over 2 weeks on hire
to
keep track of where stuff is and what is available. I have used the
following:
=IF(ISBLANK(B2),TODAY()-A2,"") this gives number of days from start of
hire
and we can put in conditional formatting to flag up after X no. of days.

where b2 = booked until date
and a2 = start of hire

Problem is that there is another field that indicates actual hire end date
and I also need to use this so that if c2 = actual end of hire then the
formula cals no of days actual hire.

Hope this makes sense!

Many thanks
--
Pandora

My problem is that I am trying to set up a spreadsheet that wiil be used by
different groups of people who historically have recored data in different
ways. I would have just put start of hire A and end of hire C. Then it would
have been if B is blank use today function to calc how many days item has
been on hire or if there is an end date then subtract A from C to indicate
no. of days item hired for. But one team records a 'hired until date' B
useful except for the fact that more often than not hires run over this date.
That team won't fill in C if there is a date in B! Arghh! So what I need to
know is
1. if there is a start date A only - How many days hire using today func
2. if there is a start date A and end of hire C - how many days hire
3. if there is a start date A and hired until date B - how many days
"expected hire"
4. If there is a start date A and hired until date B AND end of hire C How
many days actual hire i.e Cminus A.

Now I have written it out it looks even more complicated than I first thought!
I know I can't get the teams to change the dates they record and the
majority of them are anti-technology anyway. Attitude is 'why do we need
this? We always know exactly what's out and for how long.'

I'm only the person who's been told to set up a spreadsheet with flagged
dates on it!!!! Not trying to buck the culture, honest!

After getting on my soapbox, I would be very grateful for any help

Many thanks
--
Pandora


"Bob Phillips" wrote:

What is the difference between 'booked until date' and 'actual end of hire
date'?

And what do you want to know about them?

Or is simply

=IF(AND(ISBLANK(B2),ISBLANK(C2)),TODAY()-A2,"")

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Pandora" wrote in message
...
Hiya,

The spreadsheet I need function help on is to be used to flag end dates
for
plant hire. We need to know when a piece of plant is over 2 weeks on hire
to
keep track of where stuff is and what is available. I have used the
following:
=IF(ISBLANK(B2),TODAY()-A2,"") this gives number of days from start of
hire
and we can put in conditional formatting to flag up after X no. of days.

where b2 = booked until date
and a2 = start of hire

Problem is that there is another field that indicates actual hire end date
and I also need to use this so that if c2 = actual end of hire then the
formula cals no of days actual hire.

Hope this makes sense!

Many thanks
--
Pandora

Hi Pandora,

I think this works

=IF(AND(B2="",C2="",A2=""),"",IF((AND(B2="",C2="", A2<"")),TODAY()-A2+1,IF(AND(A2<"",C2<"",B2=""),C2-A2+1,IF(AND(A2<"",B2<"",C2=""),B2-A2+1,IF(AND(A2<"",B2<"",C2<""),C2-A2+1)))))

It's basically nested If statements that read that comes back with a
true or flase statement e,g

To help you read it examine what the first part is doing

IF(AND(B2="",C2="",A2=""),"",

It checks to see if A2, B2 and C2 are all blank, If they are it comes
back blank and so

Hope this helps

VBA Noob


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=552944

=IF(AND(B2="",C2=""),TODAY()-A2,IF(C2<"",C2-A2,B2-A2))

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Pandora" wrote in message
...
My problem is that I am trying to set up a spreadsheet that wiil be used
by
different groups of people who historically have recored data in different
ways. I would have just put start of hire A and end of hire C. Then it
would
have been if B is blank use today function to calc how many days item has
been on hire or if there is an end date then subtract A from C to indicate
no. of days item hired for. But one team records a 'hired until date' B
useful except for the fact that more often than not hires run over this
date.
That team won't fill in C if there is a date in B! Arghh! So what I need
to
know is
1. if there is a start date A only - How many days hire using today func
2. if there is a start date A and end of hire C - how many days hire
3. if there is a start date A and hired until date B - how many days
"expected hire"
4. If there is a start date A and hired until date B AND end of hire C How
many days actual hire i.e Cminus A.

Now I have written it out it looks even more complicated than I first
thought!
I know I can't get the teams to change the dates they record and the
majority of them are anti-technology anyway. Attitude is 'why do we need
this? We always know exactly what's out and for how long.'

I'm only the person who's been told to set up a spreadsheet with flagged
dates on it!!!! Not trying to buck the culture, honest!

After getting on my soapbox, I would be very grateful for any help

Many thanks
--
Pandora


"Bob Phillips" wrote:

What is the difference between 'booked until date' and 'actual end of
hire
date'?

And what do you want to know about them?

Or is simply

=IF(AND(ISBLANK(B2),ISBLANK(C2)),TODAY()-A2,"")

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Pandora" wrote in message
...
Hiya,

The spreadsheet I need function help on is to be used to flag end
dates
for
plant hire. We need to know when a piece of plant is over 2 weeks on
hire
to
keep track of where stuff is and what is available. I have used the
following:
=IF(ISBLANK(B2),TODAY()-A2,"") this gives number of days from start of
hire
and we can put in conditional formatting to flag up after X no. of
days.

where b2 = booked until date
and a2 = start of hire

Problem is that there is another field that indicates actual hire end
date
and I also need to use this so that if c2 = actual end of hire then
the
formula cals no of days actual hire.

Hope this makes sense!

Many thanks
--
Pandora

Hi Bob,

If say the hire was made today 17th June and returned today then your
formula would return 0.

Also if the start date is blank it still returns a value so I suggest
the following

=IF(AND(A2="",B2="",C2=""),"",IF(AND(B2="",C2=""), TODAY()-A2+1,IF(C2<"",C2-A2+1,B2-A2+1)))

VBA Noob


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=552944

Fantastic!!!! I really can't say how grateful I am, really!
--
Pandora


"VBA Noob" wrote:


Hi Pandora,

I think this works

=IF(AND(B2="",C2="",A2=""),"",IF((AND(B2="",C2="", A2<"")),TODAY()-A2+1,IF(AND(A2<"",C2<"",B2=""),C2-A2+1,IF(AND(A2<"",B2<"",C2=""),B2-A2+1,IF(AND(A2<"",B2<"",C2<""),C2-A2+1)))))

It's basically nested If statements that read that comes back with a
true or flase statement e,g

To help you read it examine what the first part is doing

IF(AND(B2="",C2="",A2=""),"",

It checks to see if A2, B2 and C2 are all blank, If they are it comes
back blank and so

Hope this helps

VBA Noob


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=552944

Thank you so much!!! I really appreciate the trouble you guys go to to answer
queries here. Thank you!!!!
--
Pandora


"Bob Phillips" wrote:

=IF(AND(B2="",C2=""),TODAY()-A2,IF(C2<"",C2-A2,B2-A2))

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Pandora" wrote in message
...
My problem is that I am trying to set up a spreadsheet that wiil be used
by
different groups of people who historically have recored data in different
ways. I would have just put start of hire A and end of hire C. Then it
would
have been if B is blank use today function to calc how many days item has
been on hire or if there is an end date then subtract A from C to indicate
no. of days item hired for. But one team records a 'hired until date' B
useful except for the fact that more often than not hires run over this
date.
That team won't fill in C if there is a date in B! Arghh! So what I need
to
know is
1. if there is a start date A only - How many days hire using today func
2. if there is a start date A and end of hire C - how many days hire
3. if there is a start date A and hired until date B - how many days
"expected hire"
4. If there is a start date A and hired until date B AND end of hire C How
many days actual hire i.e Cminus A.

Now I have written it out it looks even more complicated than I first
thought!
I know I can't get the teams to change the dates they record and the
majority of them are anti-technology anyway. Attitude is 'why do we need
this? We always know exactly what's out and for how long.'

I'm only the person who's been told to set up a spreadsheet with flagged
dates on it!!!! Not trying to buck the culture, honest!

After getting on my soapbox, I would be very grateful for any help

Many thanks
--
Pandora


"Bob Phillips" wrote:

What is the difference between 'booked until date' and 'actual end of
hire
date'?

And what do you want to know about them?

Or is simply

=IF(AND(ISBLANK(B2),ISBLANK(C2)),TODAY()-A2,"")

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Pandora" wrote in message
...
Hiya,

The spreadsheet I need function help on is to be used to flag end
dates
for
plant hire. We need to know when a piece of plant is over 2 weeks on
hire
to
keep track of where stuff is and what is available. I have used the
following:
=IF(ISBLANK(B2),TODAY()-A2,"") this gives number of days from start of
hire
and we can put in conditional formatting to flag up after X no. of
days.

where b2 = booked until date
and a2 = start of hire

Problem is that there is another field that indicates actual hire end
date
and I also need to use this so that if c2 = actual end of hire then
the
formula cals no of days actual hire.

Hope this makes sense!

Many thanks
--
Pandora