Time Calculations

G

Greg Schiedler

A2 = Hours Worked ie. 45:30:00 (45.5 hrs hh:mm:ss special format[h]:mm:ss)
B2 = Rate Decimal 15.75
C2 = =IF(A2>G2, A2-G2, "0:00:00") Figure out Overtime hours if any.
G2 = 40:00:00 Special format [h]:mm:ss

Question is can I hard code the 40 hours into the formula without refering to a
cell that contains "40:00:00"

Greg :)
 
T

Trevor Shuttleworth

Greg

one way:

=IF(A2>1.66666666666667, A2-1.66666666666667, "0:00:00")

Regards

Trevor
 
F

Frank Kabel

Hi
I forgot the second part. Make this
=IF(A2>40/24, A2-40/24, "0:00:00")


--
Regards
Frank Kabel
Frankfurt, Germany

Frank said:
Hi
try
=IF(A2>40/24, A2-G2, "0:00:00")


Greg said:
A2 = Hours Worked ie. 45:30:00 (45.5 hrs hh:mm:ss special
format[h]:mm:ss)
B2 = Rate Decimal 15.75
C2 = =IF(A2>G2, A2-G2, "0:00:00") Figure out Overtime hours if any.
G2 = 40:00:00 Special format [h]:mm:ss

Question is can I hard code the 40 hours into the formula without
refering to a cell that contains "40:00:00"

Greg :)
Click to expand...
Click to expand...
 
N

Norman Harker

Hi Greg!

Use:
=IF(A2*24>40,((A2*24)-40)/24,"00:00:00")

Interesting! I got problems with "hh:mm:ss" and with TIME(40,0,0)


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
F

Frank Kabel

Hi Greg!
Use:
=IF(A2*24>40,((A2*24)-40)/24,"00:00:00")

Interesting! I got problems with "hh:mm:ss" and with TIME(40,0,0)
Click to expand...

Hi Norman TIME is behaving exactly as stated in the help file:

:)
Frank
TIME(hour,minute,second)
....
Hour: Value between 1 and 32767. Values larger than 23 are divided by
24 and only the rest of this division is used
that ist
TIME(40,0,0) = TIME(16,0,0)
 
N

Norman Harker

Hi Frank!

Now that *is* strange!!

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
F

Frank Kabel

Norman said:
Hi Frank!

Now that *is* strange!!
Click to expand...

I think MS would call this a feature <vbg>
But you're right: seconds and minutes are 'shifted' to minutes/hours
but hours are truncated

Frank
 
B

Bob

How can I learn the basics of formula writing, with the exception of using
help ?

Frank amazes me with his knowledge !!
 
N

Norman Harker

Hi Frank!

I think that this is the problem that OP was facing. I'm sure he tried
using TIME or "hh:mm:ss" and was getting weird results.

I can't see the logic behind truncation of hours "feature".

Having said that, my preference is for cell referencing rather than
hard coding of variables; but that's another issue.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
F

Frank Kabel

Bob said:
How can I learn the basics of formula writing, with the exception of
using help ?

Frank amazes me with his knowledge !!
Click to expand...

Simple :)
lurking long enough in this NG <vbg>
and having to use Excel on a regular basis
Frank
 
N

Norman Harker

Hi Bob!

A few tips:

Hang around this newsgroup and start collecting formulas that are
interesting to you.

If you see a formula, take a close look and see if you can work out
how it works.

Start trying to answer questions posted (even if to start with you
don't have courage to post the reply).

Get the Google Search 6.0 Addin from Ron de Bruin

http://www.rondebruin.nl/Google.htm

You might also download Peter Noneley's function examples:

http://homepage.ntlworld.com/noneley/

There's also my function lists that are free to good homes that cover,
fuller than normal descriptions, source, classification, syntax and
arguments plus easy access to the help files.

And finally get John Walkenbach's Excel 2003 Formulas (Don't worry if
you don't have Excel 2003 as the differences are well flagged; John
does upgrade his texts (more than Microsoft upgrades the Versions.


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
N

Norman Harker

Aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaah!!!!!!!!!!!!!

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
M

Michael J. Malinsky

Who've thunk that a time calculation would stir up so much posting!

Perception is interesting, though. In my office, I'm considered to be
somewhat of an Excel "guru." Then I come into the NGs and feel like I know
nothing!

Hang around here long enough and you just may learn something. Even though
I'm not as good as Norman, Frank, Peo, or some of the other "regulars," I
try to give back where I can (give and take thing, right?).

So if I haven't said it enough....TIA and HTH!

--
Michael J. Malinsky
Pittsburgh, PA

"I was gratified to be able to answer promptly,
and I did. I said I didn't know." -- Mark Twain
 
B

Bob

Many thanks I'll take the advice
Bob
Norman Harker said:
Hi Bob!

A few tips:

Hang around this newsgroup and start collecting formulas that are
interesting to you.

If you see a formula, take a close look and see if you can work out
how it works.

Start trying to answer questions posted (even if to start with you
don't have courage to post the reply).

Get the Google Search 6.0 Addin from Ron de Bruin

http://www.rondebruin.nl/Google.htm

You might also download Peter Noneley's function examples:

http://homepage.ntlworld.com/noneley/

There's also my function lists that are free to good homes that cover,
fuller than normal descriptions, source, classification, syntax and
arguments plus easy access to the help files.

And finally get John Walkenbach's Excel 2003 Formulas (Don't worry if
you don't have Excel 2003 as the differences are well flagged; John
does upgrade his texts (more than Microsoft upgrades the Versions.


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
Click to expand...
 
N

Norman Harker

Hi Bob!

A balmy 28C for today.

And we play cricket as poor Sri Lanka found out yesterday (you need to
play well on all five days of a test match!)

We serve beer ice cold.

We speek inglish pritty gud.

If it wasn't for those darned politicians, this place would be heaven!

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Subtracting time...again 2
Show time as a negative figure 3
Time Sum in Excel 1
Use SUM to add time format 5
TIME CALCULATION 5
excel 2003 (11.8307.8221) SP3 - custom cell formatting question 2
Sum Time 6
Time for some help (Please!) 5

Top