Formula

  • Thread starter Thread starter Steved
  • Start date Start date
S

Steved

Hello from Steved

=IF(6900>=78000,(6900-72000),6900)/6000
=IF(78900>=78000,(78900-72000),78900)/6000
Both above will give me 1.15 decimal time which I need
=IF(900>=78000,(900-72000),900)/6000
The above will give 0.15 I would like 12.15 please
 
Hi Steved!

I won't pretend to have a clue what you're doing but try:

=IF(900>=78000,(900-72000),900+72000)/6000

--
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 Chuck!

I think Steved was getting ready to replace his hard coded numbers
with cell references. Your approach would also add 12 to the TRUE
result. That may or may not be wanted. Who knows?

--
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.
 
Thanks Norman for your reply
Your formula gives me 12.15 which is just what I wanted,
but when I put in 37200 it gives me 18.20, I would like
6.20.

I am using a decimal point for time in stead of :
ie 620 *60 is 37200 using the one you kindly gave to me
=IF(37200>=78000,(37200-72000),37200+72000)/6000
will return 18.20
=IF(37200>=78000,(37200-72000),37200)/6000
will return 6.20 hence if I could combine the two together
it will return 12.15 or 6.20

any idea's please

Thanks


Your formula gives me 12.15 which is just what I wanted
 
Hello CLR thanks for your reply
If I put =IF(109200>=78000,(109200-72000),109200)/6000+12
the returned value is 18.20 I require 6.20

The above is that I am using a Decimal point instead of :
for time and I would like to work 12hr clock hence 6.20am
or 6.20pm will always return 6.20 not 18.20 for pm

any idea's

Thankyou.
 
Steved said:
Hello CLR thanks for your reply
If I put =IF(109200>=78000,(109200-72000),109200)/6000+12
the returned value is 18.20 I require 6.20
Click to expand...

You're making everyone guess that 109200 is a variable. Also, it looks like
you're using a screwball time encoding like

=(60*HOUR(NOW()-TODAY())+MINUTE(NOW()-TODAY()))*100+SECOND(NOW()-TODAY())

If so, and you want midnight and noon appearing as 0, then just use

=MOD(ScrewballTime,72000)/6000

If you want midnight and noon to appear as 1200, then use

=1+MOD(ScrewballTime+66000,72000)/6000
 
Hi Steved!

So what you are doing is entering times

620 for 6:20
1215 for 12:15
1830 for 18:30

And what you want is 6.20, 12.15, and 18.3

Why not 620/100 etc.

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

Harlan imangine typing over 900 times the time
I has set up a formula that when they type 620 the formula
returns 6:20 A.M. and 1440 returns 2:45 P.M.
Now what you have allowed me to do is convert it back to
620 for 6:20 A.M. and 245 for 2:45 P.M.
Once again thankyou very much this formula of yours has
saved many hour of woork

Cheers
 
Back
Top