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

Platform discussions
, ,
1

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.

2

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

3

@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:

image
image762×701 43.3 KB

4

Try:

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

@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:

image
image727×381 19.6 KB

6

Share a screenshot of your formula.

7

@GCavin Sure

Here it is:

image
image514×609 18 KB

8

{Est Hours} being a numeric field, try

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

instead of

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

@GCavin

WORKED!! :slight_smile:

Thanks a lot!

10

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.