Need to Increment Date and Set Time

I have a column that contains date/time values. If the time value for that date/time exceeds 6pm, I need to increment the date by one and set the time value to 7:00AM.

My current calculation seems cumbersome and increments the day by 1 but sets the time value to 12:00AM. If I create another column and do another calculation I can just add 7 hours but I'm assuming I can do this calculation all in 1 cell versus 2 cells.

Any help is much appreciated!

Cell value is 3/14/11 8:43 PM

Current calc is: =IF(TEXT(I585, "h:mm")*1>TIME(18, 0, 0),(DATE(YEAR(I585),MONTH(I585),DAY(I585+1))), VALUE(I585))

Returns: 3/15/11 12:00 AM

Second Calc is: =IF(TEXT(J585, "h:mm")*1=TIME(0, 0, 0),(J585+(TIME(7,0,0))), VALUE(I585))

Which returns: 3/15/11 7:00 AM

How about:

Please Login or Register  to view this content.

Cheers,

Originally Posted by ConneXionLost

How about:

Please Login or Register  to view this content.

Cheers,

Apologies, I just edited out my comments. It works, UE was preventing it from being effective.

Thank you!

In that case, try:

Please Login or Register  to view this content.

Cheers,