Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Another Days, Hours, Minutes, Question
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.... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Another Days, Hours, Minutes, Question
I am not getting what your problem is, what are you looking for help with?
-- __________________________________ HTH Bob "ShagNasty" wrote in message ... 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.... |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Another Days, Hours, Minutes, Question
On Sun, 26 Oct 2008 21:34:01 -0700, ShagNasty
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Another Days, Hours, Minutes, Question
Sorry for the rambling...
I need a routine for computing a time period -- using a historical date (hh:dd:mm) subtracted from the present date (now()), output as dd:hh:mm. This will be 24hrs/day, 7 days/week, 365 days/year operation, no limit to period length. I've read messages concerning issues when calculating time over days when you want it in days, hours, minutes format -- using +1 or -1 to get correct days or minutes when exceeding 24 hours. Just looking for a better routine.. Thanks, "Bob Phillips" wrote: I am not getting what your problem is, what are you looking for help with? -- __________________________________ HTH Bob "ShagNasty" wrote in message ... 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.... |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Another Days, Hours, Minutes, Question
On Mon, 27 Oct 2008 08:16:38 -0400, Ron Rosenfeld
wrote: On Sun, 26 Oct 2008 21:34:01 -0700, ShagNasty 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Another Days, Hours, Minutes, Question
On Mon, 27 Oct 2008 08:16:38 -0400, Ron Rosenfeld
wrote: On Sun, 26 Oct 2008 21:34:01 -0700, ShagNasty 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 This one should be correct :-( =IF(B3$A$9,"-","")&TRUNC($A$9-B3)&":"&TEXT(ABS($A$9-B3-TRUNC($A$9-B3)),"hh:mm") --ron |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Another Days, Hours, Minutes, Question
Thanks Ron and Bob, This takes a few steps out of my effort..
Thanks again.. "Ron Rosenfeld" wrote: On Mon, 27 Oct 2008 08:16:38 -0400, Ron Rosenfeld wrote: On Sun, 26 Oct 2008 21:34:01 -0700, ShagNasty 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 This one should be correct :-( =IF(B3$A$9,"-","")&TRUNC($A$9-B3)&":"&TEXT(ABS($A$9-B3-TRUNC($A$9-B3)),"hh:mm") --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
converting days, hours and minutes to minutes | New Users to Excel | |||
converting Days Hours & minutes into just minutes in excel | Excel Discussion (Misc queries) | |||
Convert days in decimal to days:hours:minutes | Excel Worksheet Functions | |||
Problem converting Hours to Days, Hours, Minutes | Excel Worksheet Functions | |||
Formula for minutes to days:hours:minutes | Excel Discussion (Misc queries) |