convert hrs:min to minutes

[Tonso]

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

 

[joeu2004]

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] .

 

[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)

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

 

[The_Giant_Rat_of_Sumatra]

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)))

 

[GS]

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.

 

[Claus Busch]

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

 

[GS]

After serious thinking Claus Busch wrote :

Hi Garry,

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


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


Regards
Claus Busch

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

 

[GS]

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...

 

[GS]

Very nice! Well appreciated...

 

[The_Giant_Rat_of_Sumatra]

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.

 

[GS]

The_Giant_Rat_of_Sumatra expressed precisely :

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

I modified it for that...

=IF(AND(Start<>"",Stop<>""),ROUND(MOD(Stop-Start,1)*24,2),"")

 

[GS]

GS pretended :

=IF(AND(Start<>"",Stop<>""),ROUND(MOD(Stop-Start,1)*24,2),"")

Minutes version...

=IF(AND(Start<>"",Stop<>""),ROUND(MOD(Stop-Start,1)*1440,0),"")

 

[The_Giant_Rat_of_Sumatra]

The_Giant_Rat_of_Sumatra expressed precisely :

I modified it for that...

=IF(AND(Start<>"",Stop<>""),ROUND(MOD(Stop-Start,1)*24,2),"")

Cool. I'll be able to update my task tracker.

It has other features as well, but I did them all without any macro
involvement.

So, I am sure there are better, even easier ways to do some of what I
did, but it does seem to work. I should update the date formulas though.
Or maybe not. The way I was keeping the time being on a day to day basis
keeps it from having a "greater than 24 hours charged" issue.

Perhaps an opinion will bolster my mindset or kill me off like an
uneeded Lost character.

http://office.microsoft.com/en-us/templates/tasktrack-tool-o2k7-version-TC030006038.aspx

or:

http://tinyurl.com/3j74nzz

 

[GS]

The_Giant_Rat_of_Sumatra used his keyboard to write :

Cool. I'll be able to update my task tracker.

It has other features as well, but I did them all without any macro
involvement.

My entire ProjectManager.xls is formula driven (no macros).

So, I am sure there are better, even easier ways to do some of what I
did, but it does seem to work. I should update the date formulas though.
Or maybe not. The way I was keeping the time being on a day to day basis
keeps it from having a "greater than 24 hours charged" issue.

I get that. However, if it did span more than 24 hrs in one session the
formula covers that. Not saying that would ever happen, though, but
what 's common for me is to start one day and stop the next day (ergo
before-to-after midnight).

What I didn't understand about your formula example was why you added
24. Excel stores time as a fraction of a day. Thus, my formula adds 1
day if the date changes during the elapsed time. I didn't actually test
you formula before now, but I see the difference after just trying it
now. My formula adds 1 to the stop value to increment to the next day.
Your formula adds 24 hours to the result of the multiplication. Same
effect; - different approach!

Perhaps an opinion will bolster my mindset or kill me off like an
uneeded Lost character.

http://office.microsoft.com/en-us/templates/tasktrack-tool-o2k7-version-TC030006038.aspx

or:

http://tinyurl.com/3j74nzz

I'll check out these links later...

 

[GS]

After serious thinking The_Giant_Rat_of_Sumatra wrote :

Cool. I'll be able to update my task tracker.

It has other features as well, but I did them all without any macro
involvement.

So, I am sure there are better, even easier ways to do some of what I
did, but it does seem to work. I should update the date formulas though.
Or maybe not. The way I was keeping the time being on a day to day basis
keeps it from having a "greater than 24 hours charged" issue.

Perhaps an opinion will bolster my mindset or kill me off like an
uneeded Lost character.

http://office.microsoft.com/en-us/templates/tasktrack-tool-o2k7-version-TC030006038.aspx

or:

http://tinyurl.com/3j74nzz

Okay, I see where you got your formula. This looks like a pretty good
utility, well thought out and very easy to use. I might change some of
the cell refs to use defined names, though, so it's easier to
understand. For example, I'd make the start/stop cells in each day
section col-relative/row-relative to the 'tally' field. Not
surprisingly, I'd name them "Start" and "Stop".<g> (I hate leaving cell
refs in formulas of a 'finished' project, especially a template that
goes out to users/clients!)

I needed something that would let me store multiple projects in a
single XLS, as well as handle sub-projects under a main project. To
that end I made my own template sheet and just insert this for new
projects.

Just so you understand how I use it, a 'project' is a single worksheet.
This often is named for a client. All projects for this client will be
tracked on this sheet, and so is why I needed it to handle
sub-projects. In the same manner, I can also track app dev by
component/revision. I also use it for tracking time authoring ebooks by
chapter.

As stated in another reply to this thread, it also calcs time totals by
month/sub-project AND calcs billable amounts in the same manner.

All in all, it's a lot less complex than the solution in your link but
it's a lot more flexible for my needs.


******************************
**Regarding shorter formulas**
<You forgot the 'blank until times are filled' mode. We were not
discussing shortening.>

Just thought I'd offer my opinion on this: "the less processing a
formula does the faster and more efficiently it performs"! Makes a
significant difference when your project has Ks of formulas that need
to be calc'd. Claus's offering just blows both our formulas out of the
water.
******************************

 

[The_Giant_Rat_of_Sumatra]

Just so you understand how I use it, a 'project' is a single worksheet.
This often is named for a client. All projects for this client will be
tracked on this sheet, and so is why I needed it to handle
sub-projects. In the same manner, I can also track app dev by
component/revision. I also use it for tracking time authoring ebooks by
chapter.

As stated in another reply to this thread, it also calcs time totals by
month/sub-project AND calcs billable amounts in the same manner.

All in all, it's a lot less complex than the solution in your link but
it's a lot more flexible for my needs.

Sounds cool. Mine was simply due to the fact that I work on several
things in a given day. I am glad I do not have to worry about anything
more than a single tier hierarchy.

Then, I got into submitting templates on the MS user site. So, I
modified it so that the user could select any increment, and the drop
down boxes would follow.

I like your descriptions of your features though. This would not happen
to be something which you could submit as a free, PD template is it?

Also, I have one programming question which I asked in the programming
group, but have gotten no response as yet. I may have asked in a
complicated manner.

Could you bounce over there and see if you could give a quick script
segment, which I could loop into what I am after? The title is:
"Y/N and print job sequencer". The workbook works flawlessly. I simply
want to automate the print function, but it requires toggling sets of
cell values as well.

 

[GS]

The_Giant_Rat_of_Sumatra formulated the question :

Sounds cool. Mine was simply due to the fact that I work on several
things in a given day. I am glad I do not have to worry about anything
more than a single tier hierarchy.

Then, I got into submitting templates on the MS user site. So, I
modified it so that the user could select any increment, and the drop
down boxes would follow.

I like your descriptions of your features though. This would not happen
to be something which you could submit as a free, PD template is it?

Well, I never thought of doing that. I do give it out to clients now.
I'll look over the template to see if it's a possible candidate as
it'll need some instructional comments and so forth so using it is
self-explanatory. (I usually tutor my users by examples from my own
file. Perhaps a sample sheet is all it needs...

Also, I have one programming question which I asked in the programming
group, but have gotten no response as yet. I may have asked in a
complicated manner.

Could you bounce over there and see if you could give a quick script
segment, which I could loop into what I am after? The title is:
"Y/N and print job sequencer". The workbook works flawlessly. I simply
want to automate the print function, but it requires toggling sets of
cell values as well.

I'll have a look...

 

[GS]

After studying your TaskTracker I've concluded it would take some
revision to make it so Sheet("Task Tracker") could be used as a
template that could be copied for each week of an entire year. This
way, you could store a year's worth of data in a single file. I think
this would be worthwhile to do. (Nicer than having 52 files stored
somewhere <IMO>)

I doubt I would use TaskTracker, though, because I've been using one
file since 2004.<g>

 

[The_Giant_Rat_of_Sumatra]

After studying your TaskTracker I've concluded it would take some
revision to make it so Sheet("Task Tracker") could be used as a
template that could be copied for each week of an entire year. This
way, you could store a year's worth of data in a single file. I think
this would be worthwhile to do. (Nicer than having 52 files stored
somewhere <IMO>)

I doubt I would use TaskTracker, though, because I've been using one
file since 2004.<g>

Actually, if you look at the other listing on that MS site, you would
see where I did a 52 week version, but it is just 52 copies, and jump
links.

My 52 week blood pressure reading tracker with charts is probably a
better exercise for me.

Using the file name and having one per week worked is fine for the type
of work I perform.