I am trying to calculate a date field but it looks like i will need multiple IF's can anyone give me a hand.
The columns i have are shown below i will always have a pending value (in a date/time format)for a request generated by the system. I want to be able to calculate how long it has been pending to todays date. I will then break them down by open for 1 day, 5 days or 10 days. If any of the other fields are populated the system will update them and i will need to subtract them from Pending to get a completed cancelled or authorised time.

Cancelled¦Authorised¦Complete¦Inprogress¦Pending¦

I am trying to calculate a date field but it looks like i will need
multiple IF's can anyone give me a hand.
The columns i have are shown below i will always have a pending value
(in a date/time format)for a request generated by the system. I want to
be able to calculate how long it has been pending to todays date. I will
then break them down by open for 1 day, 5 days or 10 days. If any of the
other fields are populated the system will update them and i will need
to subtract them from Pending to get a completed cancelled or authorised
time.

Cancelled�Authorised�Complete�Inprogress�P ending�

Maybe this would help getting started, if I understand the requirement.

Put this in F2, copy down, and format as a number:
=IF(MAX(A2:C2)=0,TODAY(),MAX(A2:C2)) - E2
This would calculate either
- how long pending
or
- completed cancelled or authorised time,
depending on whether or not is has been completed, cancelled or authorised.

In another column, you could "break them down by open for ..."

Hi Duane,

I think an example workbook, with no confidential info, would be beneficial here.

If you could show a before and after with some notes on the method behind how the final figures will be reached then you'll get a far better answer, far quicker.

S.

Good idea on the sample here is a quick sample of the data normall around 50000 rows but this should give you an idea of what i am trying to do.

Thanks

Hi,

I'm still not 100% sure what you're trying to achieve here.
Could you put the data in manually and explain how you got to those figures?

That would make it much easier to see exactly what you need.

S.