I have an xl2003 workbook that in F7 subtracts a time value in F5 from
a time value in F6. The time is manually entered into F5 and F6, which
are formatted "13;30" in the time format selection. The result is in
Hrs:Min format, so 15:20 - 13:30 = 1:50. I would the result, the 1:50,
to show in minutes, in this case 110 minutes. The entered times will
always be hrs:min, no seconds entered. What must I do to get the
result in minutes only? And, how do i handle when a time starts on one
day, say 11;30PM (23:50), and ends the next day at 1:50 [01:50)?

Thanks,

Tonso

On Mar 31, 7:14*am, Tonso <wthoma...@hotmail.com> wrote:
> The result is in Hrs:Min format, so 15:20 - 13:30 = 1:50.
> I would the result, the 1:50, to show in minutes, in this
> case 110 minutes.

If you just want to change the displayed value, use the format Custom
[m] .

But note that Excel time is stored as a fraction of 1 day. So 1 hr is
1/24, 1 min is 1/1440, and 1 sec is 1/86400.

If instead you want the value of the result to be an integer, use the
following formula:

=ROUND((F6-F5)*1440,0)

The use of ROUND eliminates anomalies that arise because of the way
that Excel (and most applications normally) store numbers, namely
binary floating-point.


> And, how do i handle when a time starts on one day, say
> 11;30PM (23:50), and ends the next day at 1:50 [01:50)?

The best way: record date as well as time. Then no change is needed.

But if you record only time, then compute the difference as:

=F6-F5+(F5>F6)

formatted as Custom [m] .

Another way if you want to use the same formula in all cases...

StartTime entered in A1
StopTime entered in B1
ElapsedTime returned in C1 with the following formula:

=ROUND(IF(B1>A1,(B1-A1)*1440,IF(B1<A1,((B1+1)-A1)*1440,"")),0)

Formula assumes time values are entered correctly for AM/PM.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

On Thu, 31 Mar 2011 16:21:40 -0400, GS <(E-Mail Removed)> wrote:

>Another way if you want to use the same formula in all cases...
>
> StartTime entered in A1
> StopTime entered in B1
> ElapsedTime returned in C1 with the following formula:
>
> =ROUND(IF(B1>A1,(B1-A1)*1440,IF(B1<A1,((B1+1)-A1)*1440,"")),0)
>
>Formula assumes time values are entered correctly for AM/PM.

This is what I put in "H13" (because my two cells are G13 and F13. This
keeps the tally cell blank, unless both time cells have data. It could
be modified to use the integer version quite easily, IIRC. The times
were entered in 24 Hr format and no date info.

=IF(IF((OR(G13="",F13="")),0,IF((G13<F13),((G13-F13)*24)+24,(G13-F13)*24))=0,"",IF((OR(G13="",F13="")),0,IF((G13<F13),((G13-F13)*24)+24,(G13-F13)*24)))

> This is what I put in "H13" (because my two cells are G13 and F13. This
> keeps the tally cell blank, unless both time cells have data. It could
> be modified to use the integer version quite easily, IIRC. The times
> were entered in 24 Hr format and no date info.
>
> =IF(IF((OR(G13="",F13="")),0,IF((G13<F13),((G13-F13)*24)+24,(G13-F13)*24))=0,"",IF((OR(G13="",F13="")),0,IF((G13<F13),((G13-F13)*24)+24,(G13-F13)*24)))

I use something similar, just shorter...

What I posted was a (modified to calc minutes) version of something I
use to track time worked on projects. Here's what I use for calcing
elapsed time in hours (formatted as "#.00"), based on Start/Stop times
not being empty. (All 1 line...)

=IF(AND(Start<>"",Stop<>"",Stop>Start),(Stop-Start)*24,
IF(AND(Start<>"",Stop<>"",Stop<Start),((Stop+1)-Start)*24,
""))

Also, no date info and times are entered in AM/PM format.
(Actually, this is the result of Ctrl+; being used to record time
on the fly for work done on projects)

Start (ColAbsolute-RowRelative) is entered in ColF;
Stop (ColAbsolute-RowRelative) is entered in ColG;
ElapsedTime is calced in ColH using the above formula.

Cols I/J calc month totals (date required for each entry) and
sub-project totals. (sheet supports sub-projects under a main project)
Project billing total is calced elsewhere on the sheet based on rate
for services provided.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Hi Garry,

Am Thu, 31 Mar 2011 16:21:40 -0400 schrieb GS:

> Another way if you want to use the same formula in all cases...
>
> StartTime entered in A1
> StopTime entered in B1
> ElapsedTime returned in C1 with the following formula:
>
> =ROUND(IF(B1>A1,(B1-A1)*1440,IF(B1<A1,((B1+1)-A1)*1440,"")),0)

a little bit shorter:
=ROUND(MOD(B1-A1,1)*1440,0)


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

After serious thinking Claus Busch wrote :
> Hi Garry,
>
> Am Thu, 31 Mar 2011 16:21:40 -0400 schrieb GS:
>
>> Another way if you want to use the same formula in all cases...
>>
>> StartTime entered in A1
>> StopTime entered in B1
>> ElapsedTime returned in C1 with the following formula:
>>
>> =ROUND(IF(B1>A1,(B1-A1)*1440,IF(B1<A1,((B1+1)-A1)*1440,"")),0)
>
> a little bit shorter:
> =ROUND(MOD(B1-A1,1)*1440,0)
>
>
> Regards
> Claus Busch

Thanks Claus! I'll give this a try...

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

GS made this correction :
> ...this is the result of Ctrl+; being used...

I made a typo! The above should read:

...this is the result of Ctrl+: being used...

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Very nice! Well appreciated...

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

On Fri, 1 Apr 2011 18:29:41 +0200, Claus Busch <(E-Mail Removed)>
wrote:

>a little bit shorter:
>=ROUND(MOD(B1-A1,1)*1440,0)
>
>
>Regards
>Claus Busch


You forgot the 'blank until times are filled' mode. We were not
discussing shortening.