Time Calculations

  • Thread starter Thread starter Greg Schiedler
  • Start date Start date
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 :-)
 
Greg

one way:

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

Regards

Trevor
 
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...
 
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.
 
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)
 
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.
 
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
 
How can I learn the basics of formula writing, with the exception of using
help ?

Frank amazes me with his knowledge !!
 
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.
 
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
 
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.
 
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.
 
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
 
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...
 
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.
 
Back
Top