Dear All
I have a difficulties to change the time in this data. the time that less than 09:00 AM of previous date should be change into 21:xx PM. does any one know the formulas to change this ?
Dear All
I have a difficulties to change the time in this data. the time that less than 09:00 AM of previous date should be change into 21:xx PM. does any one know the formulas to change this ?
Last edited by azbi; 10-01-2013 at 05:13 AM.
Your file does not seem to be consistent with your text above, as you have times after 10:00pm which are not shown as needing to be changed. Do you need the date to be changed as well?
Pete
Sorry, I mean All time before 09:00PM of previous date that will gonna change become 09:xx PM
Put this in C1:
Formula:Please Login or Register to view this content.
then copy down to the bottom of your list of data (C22 in the example file).
If you want a permanent change, you can fix the values in column C and then delete column B.
Hope this helps.
Pete
thanks Pete, its work
But its for today, what about for tomorrow date, its means I should change the date become "(A1=DATE(2013,10,01)", right?
how about if I don't need change every day, I just paste the date and time into it, then the change automatically?
Any Idea Pete?
So is it always yesterday's date that you will be interested in? If so you could change it to this:
=IF(AND(A1=TODAY()-1,B1<21/24),TIME(21,MINUTE(B1),SECOND(B1)),B1)
or if it is all dates before today you can use:
=IF(AND(A1<TODAY(),B1<21/24),TIME(21,MINUTE(B1),SECOND(B1)),B1)
Hope this helps.
Pete
dear Pete
Thanks a lot, its really really what I mean
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks