Automatically changing the time to ZERO if the total time exceeds 60mins.

[Nisarga]

Hello,

I need some help with this.

I have a column where it calculates the total time taken in minutes. I want this to automaticaly show as ZERO minutes if the total time exceeds 60 minutes.

Below is the existing formula in the column:

=IFERROR(H:H-G:G,"")

I am new to using excel and I unable to come up with a way to do this.

I also need that particular cell to be highlighted in Red color.

I have tried using conditional formatting and have applied the rule to the entire colunm, however the entire column turns red without any values in it and then changes to Clear if the time is below 60mins.

How can I make it such that it only turns Red when the time exceeds 60min.?

Thank you in advance.
Nisarga.

 

[Hui]

Nisarga

Firstly, Welcome to the Chandoo.org Forums

Select the cells
Right Click, Format Cells
Apply a Custom Number format of [<0.041667]h:mm:ss;0;0

60 Min is 1/24 of a day = 0.041667 Days

 

[Nisarga]

Nisarga

Firstly, Welcome to the Chandoo.org Forums

Select the cells
Right Click, Format Cells
Apply a Custom Number format of [<0.041667]h:mm:ss;0;0

60 Min is 1/24 of a day = 0.041667 Days

Hello,

Thank you for your reply.

I have done as you have told.
However, when I do this, the value displayed is 1:00:00 AM.


Is there a way to include the logic to change the time to ZERO within the formula = IFERROR(H:H-G:G,"")

 

[pecoflyer]

What is = IFERROR(H:H-G:G,"") supposed to do? The difference between cols G and H ?
And what d you mean by total time ?
Perhaps add a sample sheet with some data and expected results

 

[Nisarga]

Hello,

Yes, let me explain.

I am trying to calculate the total time taken for a particular task.

The start time and end time are in columns H and G, which use the formula =IF(F95<>"",IF(H95="",NOW(),H95),"") with the cell format as *1:30:55PM


in the next column (I) the difference is calculated using the formula =IFERROR(H:H-G:G,"") with the cell format as [m]:ss

Now the logic is, if the time elapsed in column I, exceeds more than 60mins, then it should automatically display as ZERO minutes, as any task which takes more than 60minuts is not counted in our metrics.

I also need this to be highlighted in Red.

 

[pecoflyer]

Please post a sample file Thx

 

[Nisarga]

Hello,

I have attached the excel sheet as asked.

As soon as I enter the Task ID, the current time is automatically punched in under the Start Time.
And once I finish the task, the current time is again captured in the Stop Time.

and the difference is calculated in the next column, i.e. Time Elapsed.

So now, if the time taken for a particular task exceeds 60minutes, I need this to automatucally change to 0 mins and that cell to be highlighted in Red.

Thanks again for you help,
Nisarga.

 

[pecoflyer]

No data or expected results as requested....

 

[Nisarga]

Hello,

Sorry about that.
Please find the attached sheet with data.

You can see that there are two tasks where the time exceeds 60 mins.
I have had to manually change the time to 0 and mark them in Red color.

 

[Nisarga]

@pecoflyer

Did you check the attached sheet?
Any update on this?

 

[bosco_yip]

Hello,

Sorry about that.
Please find the attached sheet with data.

You can see that there are two tasks where the time exceeds 60 mins.
I have had to manually change the time to 0 and mark them in Red color.

Try,

1] I put the formula result in Column G for comparison

2] In G2, formula copied down :

=TEXT(E2-D2,"[>0.041667]""0:00"";[m]:ss")

and In G2, Conditional Formatting as follow picture :



Regards
Bosco

 

[Nisarga]

Hey, thank you for this.

This is what I was looking for.

one small thing, When I try to sum up the time elapsed, its not showing acurate sum.

Any idea about this?

Is it due to the TEXT formula?