Calculate Date based on Time
 

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

Calculate Date based on Time
 

=IF(C2B2,A2,A2+1)
--
David Biddulph

"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

Calculate Date based on Time
 

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:D, 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

Calculate Date based on Time
 

=IF( C1<B1,A1+1,A1)
Just be sure to format column D as Date
--
Gary''s Student - gsnu200790


"Mayte" wrote:

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

Calculate Date based on Time
 

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

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
=IF(C2B2,A2,A2+1)
--
David Biddulph

"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

Calculate Date based on Time
 

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)

"Gary''s Student" wrote:

=IF( C1<B1,A1+1,A1)
Just be sure to format column D as Date
--
Gary''s Student - gsnu200790


"Mayte" wrote:

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

Calculate Date based on Time
 

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.
--
David Biddulph

"Mayte" wrote in message
...
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)

"Gary''s Student" wrote:

=IF( C1<B1,A1+1,A1)
Just be sure to format column D as Date
--
Gary''s Student - gsnu200790


"Mayte" wrote:

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