comparing numbers

[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

 

[Jason Morin]

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)

 

[Technologist]

I tried doing that and the answer ended up being 01:00:00..

 

[Jason Morin]

Your values are numbers, not text. Use:

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

HTH
Jason
Atlanta, GA

 

[Technologist]

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

 

[Ron Rosenfeld]

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.

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