Formula for calcing dates

[Pandora]

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

 

[Bob Phillips]

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]

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

 

[VBA Noob]

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

 

[Bob Phillips]

=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)

 

[VBA Noob]

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

 

[Pandora]

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

 

[Pandora]

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