Calculate Date based on Time

[Mayte]

is there any way to use a formula in column D (Close Date) that will give you
the close date?? For insrtance

IF I have a "Close Time" that is for the same day ...File "Open Time" is at
11:50PM and "Close Time" is at 11:58PM, the "Close Date" would be the same as
the "Open date" (06/01/08)

BUT ...IF the "Close Time" is the next day .... File "Open Time" is at
11:55PM and the "Close Time" is at 1:00AM (next day), the "Close Date" would
be 06/03/08

Open Date Open Time Close Time Close Date
06/01/08 11:50 PM 11:58 PM 06/01/08
06/02/08 11:55 PM 1:00 AM 06/03/08

any ideas would be greatly appreciate it !!

cheers,
mayte

 

[David Biddulph]

=IF(C2>B2,A2,A2+1)

 

[Jim Cone]

If the files are always open less than 24 hours then
if the close time in less than the open time, add a day to the close date...
( data in columns B, with the formula in column E )
=IF(D6<C6,B6+1,B6)
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


"Mayte"
wrote in message
is there any way to use a formula in column D (Close Date) that will give you
the close date?? For insrtance
IF I have a "Close Time" that is for the same day ...File "Open Time" is at
11:50PM and "Close Time" is at 11:58PM, the "Close Date" would be the same as
the "Open date" (06/01/08)

BUT ...IF the "Close Time" is the next day .... File "Open Time" is at
11:55PM and the "Close Time" is at 1:00AM (next day), the "Close Date" would
be 06/03/08

Open Date Open Time Close Time Close Date
06/01/08 11:50 PM 11:58 PM 06/01/08
06/02/08 11:55 PM 1:00 AM 06/03/08

any ideas would be greatly appreciate it !!
cheers,
mayte

 

[Gary''s Student]

=IF( C1<B1,A1+1,A1)
Just be sure to format column D as Date

 

[David Biddulph]

.... or, more simply, =A2+(C2<B2)

 

[Mayte]

thanks to all !!!!

one last enquiry ...how do i get close date ... if I have open time 1:00AM
of 06/04/08 and close time 01:15AM of 06/05/08 (opened more than 24 hours)

 

[David Biddulph]

What information do you have that says that it it open more than 24 hours?

It's no good just putting in a closing time; you'll either need a closing
date too, or a time for which it is open.