Calculate Date based on Time

  • Thread starter Thread starter Mayte
  • Start date Start date
M

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
 
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
 
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)
 
Back
Top