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

 

[Ganjin]

hmm, the two conditions contradict each other.

ex.
if it was:
4.2.2011 10:00:00 PM
should it become:
5.2.2011 12:31:00 PM
or:
4.2.2011 12:31:00 PM

 

[smartguy]

Next day only ...

5.2.2011 12:31:00 PM

 

[Ganjin]

ah, I suffered from a bit of a stupidity on that one, sorry
don't have much experience in dealing with date/time formulas, but entering this formula into cell B2, should achieve what you want:

=IF(TIME(21,31,0)<TIME(HOUR(A1),MINUTE(A1),SECOND(A1)),A1+DAY(1)-TIME(HOUR(A1),MINUTE(A1),SECOND(A1))+TIME(12,31,0),IF(TIME(12,31,0)>TIME(HOUR(A1),MINUTE(A1),SECOND(A1)),A1-TIME(HOUR(A1),MINUTE(A1),SECOND(A1))+TIME(12,31,0),A1))

<time(hour(a1),minute(a1),second(a1)),a1+day(1)-time(hour(a1),minute(a1),second(a1))+time(12,31,0),if(time(12,31,0)><time(hour(a1),minute(a1),second(a1)),a1+day(1)-time(hour(a1),minute(a1),second(a1))+time(12,31,0),if(time(12,31,0)><time(hour(a1),minute(a1),second(a1)),a1+day(1)-time(hour(a1),minute(a1),second(a1))+time(12,31,0),if(time(12,31,0)><time(hour(a1),minute(a1),second(a1)),a1+day(1)-time(hour(a1),minute(a1),second(a1))+time(12,31,0),if(time(12,31,0)><time(hour(a1),minute(a1),second(a1)),a1+day(1)-time(hour(a1),minute(a1),second(a1))+time(12,31,0),if(time(12,31,0)>
</time(hour(a1),minute(a1),second(a1)),a1+day(1)-time(hour(a1),minute(a1),second(a1))+time(12,31,0),if(time(12,31,0)></time(hour(a1),minute(a1),second(a1)),a1+day(1)-time(hour(a1),minute(a1),second(a1))+time(12,31,0),if(time(12,31,0)></time(hour(a1),minute(a1),second(a1)),a1+day(1)-time(hour(a1),minute(a1),second(a1))+time(12,31,0),if(time(12,31,0)></time(hour(a1),minute(a1),second(a1)),a1+day(1)-time(hour(a1),minute(a1),second(a1))+time(12,31,0),if(time(12,31,0)></time(hour(a1),minute(a1),second(a1)),a1+day(1)-time(hour(a1),minute(a1),second(a1))+time(12,31,0),if(time(12,31,0)>

 

[Ganjin]

weird ...
why was it so difficult to paste the correct formula on here ...

 

[VoG]

weird ...
why was it so difficult to paste the correct formula on here ...

You need to add spaces before and after < and > otherwise the board software interprets them as HTML tags and truncates the formula.

 

[Ganjin]

ah ic, thank you

 

[smartguy]

Value Error....

Excel Workbook
	A	B
1	4.2.2011 10:00:00 PM	#VALUE!
Sheet3

 

[barry houdini]

You get a #VALUE! error presumably because A1 isn't a valid date/time, it's a text string.

Can you change A1 to a valid time/date? If you can then you can use this formula

=CHOOSE(MATCH(MOD(A1,1),{0,"12:31","21:30"}+0,0),"12:31",MOD(A1,1),"36:31")+INT(A1)

or with the current data try

=CHOOSE(MATCH(MOD(SUBSTITUTE(A1,".","/"),1),{0,"12:31","21:30"}+0),"12:31",MOD(SUBSTITUTE(A1,".","/"),1),"36:31")+INT(SUBSTITUTE(A1,".","/"))

custo format result cell as d.m.yy hh:mm:ss AM/PM

 

[smartguy]

Same Error.....