Hi,

I wish to incorporate a formula, by which I can find out whether the
activity is completed within the deadline or not.

For Eg:

My deadline for a particular activity is : 10:00 AM

I completed this activity at : 10:30AM

It should give me : MISSED

OR

If I complete the activity at 09:30 AM

It should give me : ACHIEVED

If your target time is in A1 and actual time is in B1 (both in Excel
time formats) then put this formula in C1:

=IF(B1="","",IF(B1<=A1,"Achieved","Missed"))

Hope this helps.

Pete

On Aug 3, 12:38 pm, DD wrote:
Hi,

I wish to incorporate a formula, by which I can find out whether the
activity is completed within the deadline or not.

For Eg:

My deadline for a particular activity is : 10:00 AM

I completed this activity at : 10:30AM

It should give me : MISSED

OR

If I complete the activity at 09:30 AM

It should give me : ACHIEVED

Hi Pete,

That was awesome!

Thanks

DD

"Pete_UK" wrote:

If your target time is in A1 and actual time is in B1 (both in Excel
time formats) then put this formula in C1:

=IF(B1="","",IF(B1<=A1,"Achieved","Missed"))

Hope this helps.

Pete

On Aug 3, 12:38 pm, DD wrote:
Hi,

I wish to incorporate a formula, by which I can find out whether the
activity is completed within the deadline or not.

For Eg:

My deadline for a particular activity is : 10:00 AM

I completed this activity at : 10:30AM

It should give me : MISSED

OR

If I complete the activity at 09:30 AM

It should give me : ACHIEVED

You're welcome - thanks for feeding back.

Pete

On Aug 3, 1:00 pm, DD wrote:
Hi Pete,

That was awesome!

Thanks

DD



"Pete_UK" wrote:
If your target time is in A1 and actual time is in B1 (both in Excel
time formats) then put this formula in C1:

=IF(B1="","",IF(B1<=A1,"Achieved","Missed"))

Hope this helps.

Pete

On Aug 3, 12:38 pm, DD wrote:
Hi,

I wish to incorporate a formula, by which I can find out whether the
activity is completed within the deadline or not.

For Eg:

My deadline for a particular activity is : 10:00 AM

I completed this activity at : 10:30AM

It should give me : MISSED

OR

If I complete the activity at 09:30 AM

It should give me : ACHIEVED- Hide quoted text -

- Show quoted text -

Hi Pete,

I have incorporated the formula as per your instructions, however, even
though the time formats are same it is not giving me the desire results.

Could you please help me with this?

Thanks,
DD

"DD" wrote:

Hi,

I wish to incorporate a formula, by which I can find out whether the
activity is completed within the deadline or not.

For Eg:

My deadline for a particular activity is : 10:00 AM

I completed this activity at : 10:30AM

It should give me : MISSED

OR

If I complete the activity at 09:30 AM

It should give me : ACHIEVED

You must ensure that you use Excel time formats, i.e. enter a time
just as 10:30:00 (the seconds part is optional). To get that cell
displayed as 10:30 AM you need to go to Format | Cells | Number tab
and then choose Time and then choose 1:30 PM from the list. If you are
going to enter a lot of times then you can pre-format all the cells in
the column first by highlighting them and doing the above - then you
will only need to enter 10:30, or 13:20 for example for the time.

I suspect you have been typing in "10:30 AM" (without the quotes, and
with/without a space before the AM). Excel will interpret this as text
and the formula I gave you will not work.

Hope this helps you solve the problem.

Pete

On Aug 3, 6:18 pm, DD wrote:
Hi Pete,

I have incorporated the formula as per your instructions, however, even
though the time formats are same it is not giving me the desire results.

Could you please help me with this?

Thanks,
DD



"DD" wrote:
Hi,

I wish to incorporate a formula, by which I can find out whether the
activity is completed within the deadline or not.

For Eg:

My deadline for a particular activity is : 10:00 AM

I completed this activity at : 10:30AM

It should give me : MISSED

OR

If I complete the activity at 09:30 AM

It should give me : ACHIEVED- Hide quoted text -

- Show quoted text -

Hi Pete,

The coulmn where the time is to be entered is automated and not manually
fed, it takes the system time by default. It is working on a formula:
=IF(C5="","",IF(H5="",NOW(),H5)), where C5 is the process on which I'm
working and H5 is my start time. I have used the calculation-iteration
function available in the tools option.
Similarly my end time is also automated. I have put the formula as suggested
by you in my Turn-around-time (TAT) column.

When my end time is less than the TAT, it should give me Achieved, or else
Missed.

This is working fine on any other sheet when I put the tme manually.

I have copied the format for you.

PROCESS COMPLETED TAT TAT ACHIEVED/MISSED START END
XXX XXX End 10:00:00 Missed 9:30 AM 9:30 AM

Please advice.

DD





"Pete_UK" wrote:

You must ensure that you use Excel time formats, i.e. enter a time
just as 10:30:00 (the seconds part is optional). To get that cell
displayed as 10:30 AM you need to go to Format | Cells | Number tab
and then choose Time and then choose 1:30 PM from the list. If you are
going to enter a lot of times then you can pre-format all the cells in
the column first by highlighting them and doing the above - then you
will only need to enter 10:30, or 13:20 for example for the time.

I suspect you have been typing in "10:30 AM" (without the quotes, and
with/without a space before the AM). Excel will interpret this as text
and the formula I gave you will not work.

Hope this helps you solve the problem.

Pete

On Aug 3, 6:18 pm, DD wrote:
Hi Pete,

I have incorporated the formula as per your instructions, however, even
though the time formats are same it is not giving me the desire results.

Could you please help me with this?

Thanks,
DD



"DD" wrote:
Hi,

I wish to incorporate a formula, by which I can find out whether the
activity is completed within the deadline or not.

For Eg:

My deadline for a particular activity is : 10:00 AM

I completed this activity at : 10:30AM

It should give me : MISSED

OR

If I complete the activity at 09:30 AM

It should give me : ACHIEVED- Hide quoted text -

- Show quoted text -

Instead of using NOW() in your formulae, which returns date and time,
perhaps you should use MOD(NOW(),1) - this will give you only the time
element.

Hope this helps.

Pete

On Aug 6, 2:46 pm, DD wrote:
Hi Pete,

The coulmn where the time is to be entered is automated and not manually
fed, it takes the system time by default. It is working on a formula:
=IF(C5="","",IF(H5="",NOW(),H5)), where C5 is the process on which I'm
working and H5 is my start time. I have used the calculation-iteration
function available in the tools option.
Similarly my end time is also automated. I have put the formula as suggested
by you in my Turn-around-time (TAT) column.

When my end time is less than the TAT, it should give me Achieved, or else
Missed.

This is working fine on any other sheet when I put the tme manually.

I have copied the format for you.

PROCESS COMPLETED TAT TAT ACHIEVED/MISSED START END
XXX XXX End 10:00:00 Missed 9:30 AM 9:30 AM

Please advice.

DD



"Pete_UK" wrote:
You must ensure that you use Excel time formats, i.e. enter a time
just as 10:30:00 (the seconds part is optional). To get that cell
displayed as 10:30 AM you need to go to Format | Cells | Number tab
and then choose Time and then choose 1:30 PM from the list. If you are
going to enter a lot of times then you can pre-format all the cells in
the column first by highlighting them and doing the above - then you
will only need to enter 10:30, or 13:20 for example for the time.

I suspect you have been typing in "10:30 AM" (without the quotes, and
with/without a space before the AM). Excel will interpret this as text
and the formula I gave you will not work.

Hope this helps you solve the problem.

Pete

On Aug 3, 6:18 pm, DD wrote:
Hi Pete,

I have incorporated the formula as per your instructions, however, even
though the time formats are same it is not giving me the desire results.

Could you please help me with this?

Thanks,
DD

"DD" wrote:
Hi,

I wish to incorporate a formula, by which I can find out whether the
activity is completed within the deadline or not.

For Eg:

My deadline for a particular activity is : 10:00 AM

I completed this activity at : 10:30AM

It should give me : MISSED

OR

If I complete the activity at 09:30 AM

It should give me : ACHIEVED- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Its not working, any other suggestion?

DD

"Pete_UK" wrote:

Instead of using NOW() in your formulae, which returns date and time,
perhaps you should use MOD(NOW(),1) - this will give you only the time
element.

Hope this helps.

Pete

On Aug 6, 2:46 pm, DD wrote:
Hi Pete,

The coulmn where the time is to be entered is automated and not manually
fed, it takes the system time by default. It is working on a formula:
=IF(C5="","",IF(H5="",NOW(),H5)), where C5 is the process on which I'm
working and H5 is my start time. I have used the calculation-iteration
function available in the tools option.
Similarly my end time is also automated. I have put the formula as suggested
by you in my Turn-around-time (TAT) column.

When my end time is less than the TAT, it should give me Achieved, or else
Missed.

This is working fine on any other sheet when I put the tme manually.

I have copied the format for you.

PROCESS COMPLETED TAT TAT ACHIEVED/MISSED START END
XXX XXX End 10:00:00 Missed 9:30 AM 9:30 AM

Please advice.

DD



"Pete_UK" wrote:
You must ensure that you use Excel time formats, i.e. enter a time
just as 10:30:00 (the seconds part is optional). To get that cell
displayed as 10:30 AM you need to go to Format | Cells | Number tab
and then choose Time and then choose 1:30 PM from the list. If you are
going to enter a lot of times then you can pre-format all the cells in
the column first by highlighting them and doing the above - then you
will only need to enter 10:30, or 13:20 for example for the time.

I suspect you have been typing in "10:30 AM" (without the quotes, and
with/without a space before the AM). Excel will interpret this as text
and the formula I gave you will not work.

Hope this helps you solve the problem.

Pete

On Aug 3, 6:18 pm, DD wrote:
Hi Pete,

I have incorporated the formula as per your instructions, however, even
though the time formats are same it is not giving me the desire results.

Could you please help me with this?

Thanks,
DD

"DD" wrote:
Hi,

I wish to incorporate a formula, by which I can find out whether the
activity is completed within the deadline or not.

For Eg:

My deadline for a particular activity is : 10:00 AM

I completed this activity at : 10:30AM

It should give me : MISSED

OR

If I complete the activity at 09:30 AM

It should give me : ACHIEVED- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

You could email me a small sample of your workbook and I'll look at it
later tonight.

Send to: pashurst <at auditel.net

Pete

On Aug 6, 4:02 pm, DD wrote:
Its not working, any other suggestion?

DD



"Pete_UK" wrote:
Instead of using NOW() in your formulae, which returns date and time,
perhaps you should use MOD(NOW(),1) - this will give you only the time
element.

Hope this helps.

Pete

On Aug 6, 2:46 pm, DD wrote:
Hi Pete,

The coulmn where the time is to be entered is automated and not manually
fed, it takes the system time by default. It is working on a formula:
=IF(C5="","",IF(H5="",NOW(),H5)), where C5 is the process on which I'm
working and H5 is my start time. I have used the calculation-iteration
function available in the tools option.
Similarly my end time is also automated. I have put the formula as suggested
by you in my Turn-around-time (TAT) column.

When my end time is less than the TAT, it should give me Achieved, or else
Missed.

This is working fine on any other sheet when I put the tme manually.

I have copied the format for you.

PROCESS COMPLETED TAT TAT ACHIEVED/MISSED START END
XXX XXX End 10:00:00 Missed 9:30 AM 9:30 AM

Please advice.

DD

"Pete_UK" wrote:
You must ensure that you use Excel time formats, i.e. enter a time
just as 10:30:00 (the seconds part is optional). To get that cell
displayed as 10:30 AM you need to go to Format | Cells | Number tab
and then choose Time and then choose 1:30 PM from the list. If you are
going to enter a lot of times then you can pre-format all the cells in
the column first by highlighting them and doing the above - then you
will only need to enter 10:30, or 13:20 for example for the time.

I suspect you have been typing in "10:30 AM" (without the quotes, and
with/without a space before the AM). Excel will interpret this as text
and the formula I gave you will not work.

Hope this helps you solve the problem.

Pete

On Aug 3, 6:18 pm, DD wrote:
Hi Pete,

I have incorporated the formula as per your instructions, however, even
though the time formats are same it is not giving me the desire results.

Could you please help me with this?

Thanks,
DD

"DD" wrote:
Hi,

I wish to incorporate a formula, by which I can find out whether the
activity is completed within the deadline or not.

For Eg:

My deadline for a particular activity is : 10:00 AM

I completed this activity at : 10:30AM

It should give me : MISSED

OR

If I complete the activity at 09:30 AM

It should give me : ACHIEVED- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

ok

"Pete_UK" wrote:

You could email me a small sample of your workbook and I'll look at it
later tonight.

Send to: pashurst <at auditel.net

Pete

On Aug 6, 4:02 pm, DD wrote:
Its not working, any other suggestion?

DD



"Pete_UK" wrote:
Instead of using NOW() in your formulae, which returns date and time,
perhaps you should use MOD(NOW(),1) - this will give you only the time
element.

Hope this helps.

Pete

On Aug 6, 2:46 pm, DD wrote:
Hi Pete,

The coulmn where the time is to be entered is automated and not manually
fed, it takes the system time by default. It is working on a formula:
=IF(C5="","",IF(H5="",NOW(),H5)), where C5 is the process on which I'm
working and H5 is my start time. I have used the calculation-iteration
function available in the tools option.
Similarly my end time is also automated. I have put the formula as suggested
by you in my Turn-around-time (TAT) column.

When my end time is less than the TAT, it should give me Achieved, or else
Missed.

This is working fine on any other sheet when I put the tme manually.

I have copied the format for you.

PROCESS COMPLETED TAT TAT ACHIEVED/MISSED START END
XXX XXX End 10:00:00 Missed 9:30 AM 9:30 AM

Please advice.

DD

"Pete_UK" wrote:
You must ensure that you use Excel time formats, i.e. enter a time
just as 10:30:00 (the seconds part is optional). To get that cell
displayed as 10:30 AM you need to go to Format | Cells | Number tab
and then choose Time and then choose 1:30 PM from the list. If you are
going to enter a lot of times then you can pre-format all the cells in
the column first by highlighting them and doing the above - then you
will only need to enter 10:30, or 13:20 for example for the time.

I suspect you have been typing in "10:30 AM" (without the quotes, and
with/without a space before the AM). Excel will interpret this as text
and the formula I gave you will not work.

Hope this helps you solve the problem.

Pete

On Aug 3, 6:18 pm, DD wrote:
Hi Pete,

I have incorporated the formula as per your instructions, however, even
though the time formats are same it is not giving me the desire results.

Could you please help me with this?

Thanks,
DD

"DD" wrote:
Hi,

I wish to incorporate a formula, by which I can find out whether the
activity is completed within the deadline or not.

For Eg:

My deadline for a particular activity is : 10:00 AM

I completed this activity at : 10:30AM

It should give me : MISSED

OR

If I complete the activity at 09:30 AM

It should give me : ACHIEVED- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -