Time formula

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Hi Pete,

That was awesome!

Thanks

DD

Pete_UK said:
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
Click to expand...
 
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
 
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
 
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
 
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
 
Its not working, any other suggestion?

DD

Pete_UK said:
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
Click to expand...
 
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
 
Back
Top