Some help needed?

[luvmath03]

I am not sure whether to post this here or in programming.

I am trying to add a various amounts of minutes to a time. The proble
is the format of the time. It is given increments of 10 minutes from
hours before something to 7 hours after.

Examples:


8 hours before ...+....22 minutes......7hours 38minutes before
7hours 50 min ...+....21 minutes......7hours 29minutes before
1hour 15minute....+...105minutes......0 hours 30minutes after

6hours 30min after...+..35 minutes......5minutes before

etc. Can someone help

 

[Guest]

Luvmath --
If you can live with the times in standard format -- that is, 7:58 rather
than "2 minutes before eight" -- here's an approach:

Cell A1 is your starting time:
=TIMEVALUE("4:32:00 PM")
Cell A2 is the minutes you want to add:
=TIMEVALUE("00:17:00 AM"

Just add the two together, format the cell for time, and you should be good
to go. I didn't try it w/o the seconds; I suspect that might save keystrokes
and errors.

HTH

 

[Ron Rosenfeld]

I am not sure whether to post this here or in programming.

I am trying to add a various amounts of minutes to a time. The problem
is the format of the time. It is given increments of 10 minutes from 8
hours before something to 7 hours after.

Examples:


8 hours before ...+....22 minutes......7hours 38minutes before
7hours 50 min ...+....21 minutes......7hours 29minutes before
1hour 15minute....+...105minutes......0 hours 30minutes after

6hours 30min after...+..35 minutes......5minutes before

etc. Can someone help?

What, exactly, is in the cell?

Is it a text string or a formatted time value?

If it is a formatted time value, what is the format?


--ron

 

[luvmath03]

Well, I do not actually have it fully laid out yet.

I have an extreme amount of calculating to do and getting a formula
would be really important.

One posting said to just add the times together....

That will not work. If I start a section at 6hours and 15 minutes
before "gotime" and it takes 21 minutes to finish this section then the
time at the end of that section is 5 hours and 54 minutes until "gotime"


I can do that adding for the times after "gotime" until 6 hours "after"
which is actually 6hours before go time.

Let me show some more examples.....

start time elapsed time
Finish time

5 h 30 min before 62 min 4 h
28 min before
4 h 15 min before 45 min 3 h
30 min before

0 h 43 min before 47 min 0
h 4 min after
5 h 36 min after 32 min 5
h 52 min before


notice how these times "wrap" 6 hours after is the same as 6 hours
before.

I would like to establish a new type of number.....say -6.00 or -5.45
for 6 hours 0 min before or 5 hours 45 min before respectively. -0.45
45 min before and 0.45 45 min after. then it wraps from 6.0 to a
negative "number"

 

[Ron Rosenfeld]

Well, I do not actually have it fully laid out yet.

I have an extreme amount of calculating to do and getting a formula
would be really important.

One posting said to just add the times together....

That will not work. If I start a section at 6hours and 15 minutes
before "gotime" and it takes 21 minutes to finish this section then the
time at the end of that section is 5 hours and 54 minutes until "gotime"


I can do that adding for the times after "gotime" until 6 hours "after"
which is actually 6hours before go time.

Let me show some more examples.....

start time elapsed time
Finish time

5 h 30 min before 62 min 4 h
28 min before
4 h 15 min before 45 min 3 h
30 min before

0 h 43 min before 47 min 0
h 4 min after
5 h 36 min after 32 min 5
h 52 min before


notice how these times "wrap" 6 hours after is the same as 6 hours
before.

I would like to establish a new type of number.....say -6.00 or -5.45
for 6 hours 0 min before or 5 hours 45 min before respectively. -0.45
45 min before and 0.45 45 min after. then it wraps from 6.0 to a
negative "number"

Does it make a difference if you are 3 hrs before "go time" vs 3 hours after?

First of all, in order to express negative times in an Excel time format you
MUST CHANGE to the 1904 date system for the workbook. This will alter any
previously entered dates in that workbook by 4 years.
Tools/Options/Calculation/Workbook Options and SELECT 1904 system.

You could use the following formulas and formats to replicate your data above.

C2 =TIME(6,,)

Format/Cells/Number/Custom Type:
[<0]h "hours "m" minutes before";h "hours "m" minutes after"

Entry Start Elapsed Finish
5:30 =TIME(,,)-A2 62 =MOD(B2+TIME(,$C$2,),$H$1*SIGN(B2+TIME(,$C$2,)))

Select B22 and copy/drag down

The results:

Data
Entry Start Elapsed Finish
5:30 5 hours 30 minutes before 62 4 hours 28 minutes before
4:15 4 hours 15 minutes before 45 3 hours 30 minutes before
0:43 0 hours 43 minutes before 47 0 hours 4 minutes after
5:36 5 hours 36 minutes before 32 5 hours 4 minutes before

Maybe this can get you started with what you want to do.
--ron