>1 hh displays in minutes. Display in hour+?

[StargateFan]

Sorry, I've looked everywhere, through google and all my saved
spreadsheets but I don't remember enough to have brought up any kind
of useful result.

I have a spreadsheet that is tracking time. I have the format set to
HH:mm:ss and enter 375000, for example, for a 37.5 hh workweek.

In the column that deducts for overtime, I have a couple of entries
like this:

00:74:00
00:81:00

where
00:74:00 would be better as 01:14:00 for 1 hour and 14 minutes, and
00:81:00 should display as 01:21:00.

How can we this be done pls? I don't see how to apply anything in
http://www.cpearson.com/excel/datearith.htm to this particular case.
I need the exact minutes so rounding not needed and the part about
subtracting time I don't see how it will work.

Here's the data, which I hope lines up as well for people reading as
it's doing this end <g>:

A B C D E F

HH shud Actual session time lieu balance
WEEK wrked HH wrkd O/T pay owing

dates 37:50:00 37:52:00 00:02:00 -0- 00:02:00
dates 37:50:00 38:24:00 00:74:00 -0- 00:76:00
dates 37:50:00 38:31:00 00:81:00 -0- 01:57:00
dates 30:00:00 33:04:00 03:04:00 -0- 04:61:00

Column D, E and F will be problem ones whereas B and C are not since
time is entered in them manually.

[Column E is empty as tiem in lieu of pay has not been taken. That's
why the spreadsheet is needed since overtime is never paid.]

Formula in D3 =IF($C3<>"";SUM($C3-$B3);"")
Formula in F3: =IF($C3<>"";($F2+$D3)-$E3;"")

These cells are formatted as 00:00:00, if memory serves. My XL2K here
at home is corrupted so I had to open this in OpenOfficeCalc and it
does the whole formatting thing differently. So will continue work at
office on this.

Thanks! As always, help is much appreciated. D

 

[Jerry W. Lewis]

Excel 2003 behaves as you desire, not as you describe. What version of Excel
do you have at the office? What format(s) are you using (in the Format
Cells|Number dialog, go to the Custom category and note the Type string)?
Your formula for column D presumes that no one will work less than scheduled,
which likely will not always be true. Also, why are you displaying seconds
if you do not want or use them?

The data in your example does not add up. For example, 38 hours and 24
minutes is only 34 minutes over 37 hours and 50 minutes, not 74 minutes over
as your table shows.

Jerry

 

[Peo Sjoblom]

Formatting has nothing to do with how you enter the value,
you need to enter it as 37:30 for 37.5 or skip time format
and enter it as 37.5. If you want a conversion into real time
you can go here instead


http://www.cpearson.com/excel/DateTimeEntry.htm


meaning that you would need an event macro (code is at that link)




Else you would need extra cells with help formulas

--


Regards,


Peo Sjoblom

 

[MitchellWMA]

Okay, have been trying to figure this out since I saw the responses.
That's why I guess I was so eager to find my one spreadsheet that
dealt with time. I've had time spreadsheets before, but not like that
one. All the others have just dealt with displaying time, really,
with only minor manipulations. That's where the trouble lies, I now
know fully to the point where I won't forget as easily <g>. I hadn't
even noticed the problems with the addition at that point, what with
all the other challenges.

I understand about display being display and the rest is the rest.
But how to deal with it? That's the question.

When I format the cells as HH"h"mm, and then entered 37.5 (not the
ideal entry type, but that's what I trialed first), I'd get the right
display but just like what is said in the link above re time issues,
Excel reads the 37.5 hours as "1937.05.01 00:00:00", which shows up
as 00h00. No good. So then I went on to try something else.

If I kept display at 00:00:00 and entered 37.5, I get 00:00:38. No
good either.

But when I tried display of 00:00:00 and entered 375000, the display
at least, was right. I get 37:50:00. So, okay. It was stupid. But
there you go. I'm trying to do other stuff besides this although this
overtime sheet is becoming urgent the longer it takes. I don't know
if I'm owed time or not and although when I have a chance, I'll sit
down and calculate it all; I just want to avoid that. We're heading
for some deadlines and overtime will become an issue.

So what to do? And why is the format so critical? Because I'm
tracking right down to the minute. And because the actual tracker
program gives this type of output for each day and for the total week:


Thursday
=========
09:40
12:45
13:18
18:20
=========
08 h, 07 m


TOTAL TIME WORKED: 38 h, 03 m

I've read that sheet above several times but I can't see a
workaround. I just don't now how to do this. Is there anything that
can be done using actual time? It'll be too much of a pain to start
translating all the times to decimals!

Thanks. <g>

 

[StargateFan]

Sorry, I've looked everywhere, through google and all my saved
spreadsheets but I don't remember enough to have brought up any kind
of useful result.

I have a spreadsheet that is tracking time. I have the format set to
HH:mm:ss and enter 375000, for example, for a 37.5 hh workweek.

In the column that deducts for overtime, I have a couple of entries
like this:

00:74:00
00:81:00

where
00:74:00 would be better as 01:14:00 for 1 hour and 14 minutes, and
00:81:00 should display as 01:21:00.

How can we this be done pls? I don't see how to apply anything in
http://www.cpearson.com/excel/datearith.htm to this particular case.
I need the exact minutes so rounding not needed and the part about
subtracting time I don't see how it will work.

Here's the data, which I hope lines up as well for people reading as
it's doing this end <g>:

A B C D E F

HH shud Actual session time lieu balance
WEEK wrked HH wrkd O/T pay owing

dates 37:50:00 37:52:00 00:02:00 -0- 00:02:00
dates 37:50:00 38:24:00 00:74:00 -0- 00:76:00
dates 37:50:00 38:31:00 00:81:00 -0- 01:57:00
dates 30:00:00 33:04:00 03:04:00 -0- 04:61:00

Column D, E and F will be problem ones whereas B and C are not since
time is entered in them manually.

[Column E is empty as tiem in lieu of pay has not been taken. That's
why the spreadsheet is needed since overtime is never paid.]

Formula in D3 =IF($C3<>"";SUM($C3-$B3);"")
Formula in F3: =IF($C3<>"";($F2+$D3)-$E3;"")

These cells are formatted as 00:00:00, if memory serves. My XL2K here
at home is corrupted so I had to open this in OpenOfficeCalc and it
does the whole formatting thing differently. So will continue work at
office on this.

Thanks! As always, help is much appreciated. D

Found it! I literally stumbled upon a link that explains what to do.
Though I was looking for this type of info, finding the exact thing is
often tricky, esp. when one considers how long I've been looking over
the last few days <g>.
http://www.officearticles.com/excel/adding_or_summing_time_in_microsoft_excel.htm

Though I recvd help from the ng last time, this did the trick
perfectly, too <g>. I didn't remember it incorrectly, I just didn't
have all the details fresh in my mind -- the trick was in the
formatting alright. I knew I had that part right! I must have more
faith in myself! <g>:

I ended up using

[hh]"h"mm

whereas the website uses a slight variation, a more standard format:

[h]:mm

but same difference.

And I have to remember to enter hours and minutes, something I kept
getting mixed up about since my new agency enters in hours and
minutes whereas every other agency in last 19 years has used an hours,
decimal, minutes approach. Enough to keep one on one's toes. The
former is easier and fits better since my time tracker software is in
hours and minutes, too.

So 37.5 hours is typed in this spreadsheet as 37:30.

So I literally just needed to change the format and then type in
minutes. Also, I checked the math for everything this time. I have
another fantastic freeware app called Time Sheet Calculator that
allows me to add and subtract time and it's now correct. And can't
get over that I only had to make basically 2 small modifications to
the entire thing! <g>

Also, the [h] around the custom hour format is what keeps it as hours
rather than as a time on the clock, which is what my subject line was
all about! <g>

The results work out. This is how it looks like now:

A B C D E F

HH shud Actual session time lieu balance
WEEK wrked HH wrkd O/T pay owing

dates 37h30 37h52 00h22 00h22
dates 37h30 38h24 00h54 01h16
dates 37h30 38h31 01h01 02h17
dates 30h00 33h04 03h04 05h21
dates 37h30 38h03 00h33 05h54

Tomorrow I can go in and check the lieu time I've taken so that my
balance is up-to-date and I can finally report to my supervisors once
they ask me.

Thanks! D

 

[StargateFan]

On Thu, 20 Nov 2008 07:47:38 -0500, StargateFan

[snip]

I ended up using

[hh]"h"mm

whereas the website uses a slight variation, a more standard format:

[h]:mm

but same difference.

[snip]

For the nitpicky, the website displays [h]:mm:ss but goes on to say:

"Note the square brackets around the hour format. If desired, just
delete the last 3 characters in the Type box so you don't see the
seconds values."

<g>

 

[Pauley Mike]

Bonjour® MitchellWMA avec ferveur ;o))) vous nous disiez :

Just use a decimal time entry event conversion !!!

37.5 meaning 37:30:00
OK ?

Private Sub Worksheet_Change(ByVal Target As Range)
'----- restricted entry area
If Application.Intersect(Target, Range("B3:C20")) Is Nothing Then
Exit Sub
End If
'-----some controls
If Target.Cells.Count > 1 _
Or Target.Value = "" _
Or Not (IsNumeric(Target.Value)) Then
Exit Sub
End If
'-------------
Application.EnableEvents = False
Target = Target / 24 ' ---- Decimal Time to Excel time value
Target.NumberFormat = "[h]:mm:ss;@" '----- format over 24:00
'-------- auto formula for column 4
Cells(Target.Row, 4).FormulaR1C1 = "=IF(RC3<>"""",SUM(RC3-RC2),"""")"
Cells(Target.Row, 4).NumberFormat = "[h]:mm:ss;@"
'-------- auto formula for column 6
Cells(Target.Row, 6).FormulaR1C1 = "=IF(RC3<>"""",(R[-1]C6+RC4)-RC5,"""")"
Cells(Target.Row, 6).NumberFormat = "[h]:mm:ss;@"

Application.EnableEvents = True
End Sub