# Formula the change the time

1. ## Formula the change the time

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 ?

2. ## Re: Formula the change the time

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

3. ## Re: Formula the change the time

Sorry, I mean All time before 09:00PM of previous date that will gonna change become 09:xx PM

4. ## Re: Formula the change the time

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

5. ## Re: Formula the change the time

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?

6. ## Re: Formula the change the time

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

7. ## Re: Formula the change the time

dear Pete

Thanks a lot, its really really what I mean

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1