Created a time difference through a formula, now looking for time difference status

Hello, I have created three columns:

  1. Est Hours (Number field)
  2. Time Spent (Time Tracking)
  3. Diff in time (Difference between Est Hours & Time Spent) with this formula:
ROUND(({Est Hours}-({Time Spent#Minutes})),1)&" minutes"

Now I’m looking for a time status field (4th column) which tells if the task is done on time or not.

If the value is positive it means task is done on time, if the value is negative it means the task is not done on time or delayed & if it is 0 it means nothing, the status should be blank then.

Please help me out in creating a formula for the 4th column where we could generate a time status result through the diff in time field. Or there is any other way of doing this other than a formula.

Attaching an image:
image

Let me know its possibilities.

Hi @nitishguglani :wave: ,

Welcome to the monday community!

This should work out for you.

image


IF({Time Spent#Minutes} <= {Est Hours}, IF({Time Spent#Minutes} = {Est Hours}, "Just On Time", "On Time"), "Overdue")

Let me know if this helps!

Giannis, Implementation Consultant at thespelas.com

@GiannisKoukounas
Hey thanks for resolving and the formula works well.

But I’m stuck with one more issue at the moment. It is showing “on time” for tasks that are not yet started or not even allotted the Estimated hours, is it possible to keep it blank if there time spent field is empty?

Attaching the screenshot below:

Try:

IF({Est Hours}="","",
    IF({Time Spent#Minutes} <= {Est Hours}, IF({Time Spent#Minutes} = {Est Hours}, "Just On Time", "On Time"), "Overdue")
)

@GCavin Thanks for replying, but there is something wrong with the formula. It is not showing in blank if Est Hours is blank.

Here is the Screenshot:

Share a screenshot of your formula.

@GCavin Sure

Here it is:

{Est Hours} being a numeric field, try

IF({Est Hours}="0","", ...

instead of

IF({Est Hours}="","", ...

@GCavin

WORKED!! :slight_smile:

Thanks a lot!

Hi, I’ve read this threa dwith interest as I have a similar but not idential requirement. I have an Estimated Time (numbers) column (in minutes… changable), and a time tracking column showing Actual Time worked on each task. I simply want a third column to calculate the difference between Estimated and Actual and display it as a %. I this possible? Thanks for any guidance you can offer.

Hi everyone! :wave:

If you are looking for a time status field, you can try Time in Status app which helps to track time the Item has spent in each workflow status easily. So you can monitor how fast the Item moves between statuses.
You’ll get user-friendly data on the board in a few seconds.

Also you will be able to track how much time your issue spends in each status and much more by generating:

:chart_with_upwards_trend: And use charts to visualize your reports a sample of a certain number according to time range/item range, or a single item:

  • Pie Charts
  • Bar Charts
  • Area Charts

The app is also available in the format of board integration.

You can try it on the Monday Marketplace and share your experience with us.
Hope you find this app helpful.