comparing numbers

  • Thread starter Thread starter Technologist
  • Start date Start date
T

Technologist

I'm looking for a way to compare numbers that are not in time format
but representing time. Let me give you an example:

0800 - 0730 is 70

0830 - 0800 is 30

but in time, they are both the same. Since I'm looking into doing thi
in a format other than time, I figured that the best way to do it woul
be to use an IF statement.

What I'm wondering is whether or not there is a way to make an I
statement where you compare the last two numbers only (such as the 0
and the 30)? Any and all assistance would be greatly appreciated
 
I would just convert to time and then compare.

A1: 0730
B1: =REPLACE(A1,3,,":")*1

Format B1 as time.

HTH
Jason
Atlanta, GA
(e-mail address removed)
 
Your values are numbers, not text. Use:

=REPLACE(TEXT(A1,"0000"),3,,":")*1

HTH
Jason
Atlanta, GA
 
I think I may have figured out an answer. I post it here so if someon
notices something wrong with my logic, please point it out...

=IF((G21-F21)/100 > 0.6, ((G21-40)-F21)/100, (G21-F21)/100
 
I'm looking for a way to compare numbers that are not in time format,
but representing time. Let me give you an example:

0800 - 0730 is 70

0830 - 0800 is 30

but in time, they are both the same. Since I'm looking into doing this
in a format other than time, I figured that the best way to do it would
be to use an IF statement.

What I'm wondering is whether or not there is a way to make an IF
statement where you compare the last two numbers only (such as the 00
and the 30)? Any and all assistance would be greatly appreciated.
Click to expand...

To convert a time in the format of hhmm to decimal time, use the formula:

=INT(A1/100)+MOD(A1,100)/60

That would convert, for example, 0730 into 7.5

It should not matter if the numbers are TEXT or NUMBERS.

If you want to convert it into an Excel time equivalent, divide the result by
24 and format as hh:mm.

To obtain the time difference with the format you are using, with the times in
A1 and B1:

=INT(A1/100)+MOD(A1,100)/60-(INT(B1/100)+MOD(B1,100)/60)




--ron
 
Back
Top