simple help with comparing dates in excel

  • Thread starter Thread starter forevercalz
  • Start date Start date
F

forevercalz

i have this column B named
Update Time
2005080112:22:35
2005080112:46:20

and i have column F named
New Shift end
20050801 20:30
20050801 19:30


i need to compare column F and column B together.
if column F the time is greater than column B..then it will display
greater in column i..

how do i do that? anyone please help....thanks :P ur help is greatly
appreciated:)
 
The following formula will convert the two strings into date serial values
(the first acts on column B, the second on column F)

formula 1
=DATE(LEFT(B1,4),MID(B1,5,2),MID(B1,7,2))&TIME(MID(B1,9,2),MID(B1,12,2),MID(
B1,15,2))

formula 2
=DATE(LEFT(F1,4),MID(F1,5,2),MID(F1,7,2))&TIME(MID(F1,10,2),MID(F1,13,2),0)

You can then apply a logical test to check if F is greater than be in column
I e.g......

=IF(formula2 > formula1,"Greater","Less")

Combining them yields

=IF(DATE(LEFT(F2,4),MID(F2,5,2),MID(F2,7,2))&TIME(MID(F2,10,2),MID(F2,13,2),
0)>DATE(LEFT(B2,4),MID(B2,5,2),MID(B2,7,2))&TIME(MID(B2,9,2),MID(B2,12,2),MI
D(B2,15,2)),"Greater","Less")

Messy but it works !

--
Cheers
Nigel



"forevercalz" <[email protected]>
wrote in message
news:[email protected]...
 
i have a very very long list of data...so to compare ......will there b
a script to call all the columns in F to compare to column B....
 
Back
Top