Work out elapsed time with a dynamic clock

**er101** · 05-11-2016, 07:36 AM

Hello all,

I'm trying to work out elapsed time in excel using a start time and a dynamic clock. I want any times that exceed 24 hours from the 'In Order' time to turn the cell red. I can get it to work out elapsed time using the following formula =IF(D5 ="","",$J$4-D5) , but when the time is input as 23:00 it returns #####.

The image below should explain what im trying to achieve:-
dynamic clock.png

Any ideas what I can do in this instance? Is the formula i'm using any good?

Thank you.

**Tony Valko** · 05-11-2016, 07:45 AM

Many members are unable to see images in the *.png format due to forum compatibility issues with some browsers.

If you need to post an image post it in the *.jpg format.

Even better than posting images... post a SMALL sample file. That way we can test solutions directly in the file with the relevant data.

**er101** · 05-11-2016, 07:54 AM

Hi Tony, thanks for the advice. Please find a .jpg image below.

dynamic clock.jpg

**Tony Valko** · 05-11-2016, 07:58 AM

What's in J4?

**Phuocam** · 05-11-2016, 07:58 AM

Try ...
=IF(D5="","",MOD($J$4-D5,1))

**er101** · 05-11-2016, 08:06 AM

Hi Tony, in J4 is a VBA code that i found on the net.

Code is as follows:-

Please Login or Register  to view this content.

**Tony Valko** · 05-11-2016, 08:10 AM

What's the actual cell value?

**er101** · 05-11-2016, 08:14 AM

Phuocam, that solution has worked, but now i'm faced with another issue; when it counts past 24 hours elasped time it resets back to 0, I assume i'd need to include the start date into this forumla too?

I appreciate the help - just to add i'm very new to all of this!

**er101** · 05-11-2016, 08:16 AM

Originally Posted by Tony Valko

What's the actual cell value?

Tony, i'm not sure I follow. What do you mean by cell value?

**Tony Valko** · 05-11-2016, 08:19 AM

Select cell J4.

What is showing in the formula bar?

**er101** · 05-11-2016, 08:21 AM

Nothing other than the current time...

**Tony Valko** · 05-11-2016, 08:25 AM

If the value of cell J4 is less than the value of D5 you'll get the cell filled with hash marks.

By default Excel doesn't recognize negative time.

The formula suggested by Phuocam should work.

**er101** · 05-11-2016, 08:29 AM

It does indeed, thank you both for your help.

1 more question - is there a way to get the elapsed time to count past 24, for example 25 hours, so that it does'nt reset to 0 at 23:59:59?

**Tony Valko** · 05-11-2016, 08:33 AM

Format the cell as [h]:mm:ss.

The brackets [ ] keep the time from rolling over into days.

**er101** · 05-11-2016, 08:49 AM

Thanks Tony, I've tried your suggestion and formatted the elapsed time cell to [h]:mm:ss, but it still resets to 0 when it counts past 23:59:59, any idea where i'm going wrong?

Apologies for the additional questions.

**Tony Valko** · 05-11-2016, 09:32 AM

Can you post your file so someone can see what's happening?

I won't download files that contain VBA.

**er101** · 05-11-2016, 10:07 AM

Hi Tony, Workbook attached.

All please note this Workbook contains VBA.

Many thanks

**xladept** · 05-11-2016, 11:25 AM

Hey er101,

It works on my machine

But without the start date how can it know how many days have gone by??

Try :

Formula:

Please Login or Register  to view this content.

**er101** · 05-11-2016, 11:31 AM

Originally Posted by xladept

Hey er101,

It works on my machine

But without the start date how can it know how many days have gone by??

Ah ok, so I'd need to include the start date, that makes sense.
I'll be honest, I've no idea how to amend the formula to do that, any suggestions, is it even possible?

Many thanks.

**xladept** · 05-11-2016, 11:53 AM

Check out my previous post - I've edited it

But you'll probably want to stop that clock?

**er101** · 05-11-2016, 12:18 PM

Hi Xladept, thank you for the help, i've tried your suggested formula above but it still resets to 0.

**er101** · 05-11-2016, 12:22 PM

I'm still trying to learn formulas on a subscription website (not sure if i can mention it) but have to admit i'm not fully understanding it!

**xladept** · 05-11-2016, 12:29 PM

Try this:

Please Login or Register  to view this content.

**er101** · 05-11-2016, 12:35 PM

Works like a charm, thank you xladept! Now i've got to try and work out how it's done!

**xladept** · 05-11-2016, 12:37 PM

I think the modulo was causing it to reset. Glad to hear it's working! - You're welcome!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Work out elapsed time with a dynamic clock

Re: Work out elapsed time with a dynamic clock

Re: Work out elapsed time with a dynamic clock

Re: Work out elapsed time with a dynamic clock

Re: Work out elapsed time with a dynamic clock

Re: Work out elapsed time with a dynamic clock

Re: Work out elapsed time with a dynamic clock

Re: Work out elapsed time with a dynamic clock

Re: Work out elapsed time with a dynamic clock

Re: Work out elapsed time with a dynamic clock

Re: Work out elapsed time with a dynamic clock

Re: Work out elapsed time with a dynamic clock

Re: Work out elapsed time with a dynamic clock

Re: Work out elapsed time with a dynamic clock

Re: Work out elapsed time with a dynamic clock

Re: Work out elapsed time with a dynamic clock

Re: Work out elapsed time with a dynamic clock

Re: Work out elapsed time with a dynamic clock

Re: Work out elapsed time with a dynamic clock

Re: Work out elapsed time with a dynamic clock

Re: Work out elapsed time with a dynamic clock

Re: Work out elapsed time with a dynamic clock

Re: Work out elapsed time with a dynamic clock

Re: Work out elapsed time with a dynamic clock

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Elapsed time / clock frustration

[SOLVED] how do i work out how many hrs have passed in a 24 hr clock, in ex

Bookmarks

Bookmarks

Posting Permissions