Date & Time Change

[smartguy]

Hello All,

I have data in the below format.

Excel Workbook
	A
1	4.2.2011 09:31:42 PM
2	4.2.2011 08:53:42 PM
3	4.2.2011 06:53:42 PM
4	4.2.2011 11:53:42 PM
Sheet1

I want to change the date and time based on 2 Condition.

No : 1

If time is after 9.30 p.m i want to change the date and time in to next day 12.31 p.M.

No : 2

if time is Before 12.31 P.M i want to change the date and time in to that day 12.31 P.M

Answer :

Excel Workbook
	A	B
1	4.2.2011 09:31:42 PM	5.2.2011 12:31:00 PM
2	4.2.2011 08:53:42 PM	4.2.2011 08:53:42 PM
3	4.2.2011 06:53:42 PM	4.2.2011 06:53:42 PM
4	4.2.2011 11:53:42 PM	4.2.2011 12:31:00 PM
Sheet1

Please help to provide the formula....

 

[barry houdini]

with both?

 

[smartguy]

Yes Both......