Re: Another Days, Hours, Minutes, Question

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

On Mon, 27 Oct 2008 08:16:38 -0400, Ron Rosenfeld <ronrosenfeld@xxxxxxxxxx>
wrote:

On Sun, 26 Oct 2008 21:34:01 -0700, ShagNasty
<ShagNasty@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

Reading through the Discussion Group I find many Date/Time questions and
answers but none I can find that meets my exact needs. The topics seem to
address Hours, Days, with or w/o weekends, holiday, work hours, or work days,
etc. ? not just vanilla output containing the calculated Day(s), Hours, and
Minutes between two dates.

Below is an example of a small spreadsheet used to capture equipment runtime
for history and other tasks. I've used this spreadsheet for several years
(my first attempt at excel ? too embarrassed to show formula) and sometimes I
am suspect of the results. I truly believe that one of you gifted ladies
and/or gentlemen can provide me with a simple Macro to end my doubts?

Thanks..

A B C
1 Start Time Current
2 Name Date / Time Run Time
3 Widget 1 04/24/2008 22:13 dd:hh:mm
4 Widget 2 10/12/2008 20:55 dd:hh:mm
5 Widget 3 10/02/2008 04:19 dd:hh:mm
6 Widget 4 11/06/2008 23:59 dd:hh:mm
7
8
9 =now()

-- 24 x 7 x 365 Industry -- forget weekends, holidays, work hours, etc.?
-- 24 hour time format
-- Cell A9 would contain present time/date function -- now() or today()
whichever is best.
-- C3 would contain A9-B3 in dd:hh:mm
-- C4 would contain A9-B4 in dd:hh:mm
-- C5 would contain A9-B5 in dd:hh:mm
-- C6 would contain A9-B6 in dd:hh:mm
-- Data in B3, B4, B5, & B6 will be input when Widget is started
-- If the widget is going to be "off" for an extended time period, I will
input the planned future start time/date so employees can see remaining time
"countdown" till startup
-- Widget runtime is not predicable (wish it was) ? maybe hours, days,
months, or years..

Thanks again -- Shagnasty....

There are a few issues to be considered.

First of all, your result will have to be a text string, as the dd format will
not go over 31.

There can also be some issues with negative numbers.

So, as one possible solution, you could use this formula to display the results
in the format you request:

=IF($A$9<B3,"-","")&ABS(TRUNC($A$9-B3))&":"&TEXT(ABS(MOD($A$9-B3,1)),"hh:mm")

--ron

Correction, so that negative values are properly displayed:

=TRUNC(C3)&":"&TEXT(ABS(C3-TRUNC(C3)),"hh:mm")

--ron
.

Relevant Pages

  • Re: Another Days, Hours, Minutes, Question
    ... address Hours, Days, with or w/o weekends, holiday, work hours, or work days, ... Below is an example of a small spreadsheet used to capture equipment runtime ... -- 24 hour time format ... First of all, your result will have to be a text string, as the dd format will ...
    (microsoft.public.excel.programming)
  • Re: Another Days, Hours, Minutes, Question
    ... address Hours, Days, with or w/o weekends, holiday, work hours, or work days, ... Below is an example of a small spreadsheet used to capture equipment runtime ... -- 24 hour time format ... First of all, your result will have to be a text string, as the dd format will ...
    (microsoft.public.excel.programming)
  • Re: Another Days, Hours, Minutes, Question
    ... "Ron Rosenfeld" wrote: ... Below is an example of a small spreadsheet used to capture equipment runtime ... -- 24 hour time format ... First of all, your result will have to be a text string, as the dd format will ...
    (microsoft.public.excel.programming)
  • RE: subtraction with dates
    ... I have a spreadsheet that does this and as long as the date is entered in ... format, ... cell from each other should do the trick. ... > and should equal 21 days.(UK time format) ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Excel 2007 high cpu usage, format cell, wrap text
    ... When she highlights the whole spreadsheet, and selects Format ... If the data cells are all contiguous, select any cell in the range and press ... This is necessary as Excel 2007 uses a lot of page memory. ...
    (microsoft.public.excel.misc)