+ Reply to Thread
Results 1 to 7 of 7

Formula the change the time

  1. #1
    Forum Contributor
    Join Date
    07-06-2013
    Location
    Borneo
    MS-Off Ver
    2016
    Posts
    277

    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 ?
    Attached Files Attached Files
    Last edited by azbi; 10-01-2013 at 05:13 AM.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,598

    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. #3
    Forum Contributor
    Join Date
    07-06-2013
    Location
    Borneo
    MS-Off Ver
    2016
    Posts
    277

    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. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,598

    Re: Formula the change the time

    Put this in C1:

    Formula: copy to clipboard
    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. #5
    Forum Contributor
    Join Date
    07-06-2013
    Location
    Borneo
    MS-Off Ver
    2016
    Posts
    277

    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. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,598

    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. #7
    Forum Contributor
    Join Date
    07-06-2013
    Location
    Borneo
    MS-Off Ver
    2016
    Posts
    277

    Re: Formula the change the time

    dear Pete

    Thanks a lot, its really really what I mean

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 3
    Last Post: 11-06-2012, 01:37 AM
  2. Replies: 4
    Last Post: 09-10-2009, 02:56 AM
  3. revolving time change formula
    By reggie1000 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-27-2009, 10:42 AM
  4. Can a formula change a cells colour after a given length of time?
    By Clive Humphries in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-20-2006, 06:30 PM
  5. Help with time formula so the time will not change.
    By Joker in forum Excel General
    Replies: 1
    Last Post: 02-17-2006, 05:10 AM

Bookmarks

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