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
--
Pandora

[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" <[email protected]> wrote in message
news:[email protected]...
> 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

[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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

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

"Pandora" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> >
> >
> >

[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


"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
>
>

[Pandora]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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
> > >
> > >
> > >
>
>
>