+ Reply to Thread
Results 1 to 7 of 7

Add 2 hours to Cell containing date/time

  1. 11-25-2020, 04:49 AM #1
    lordfa9
    lordfa9 is offline
    Forum Contributor
    Join Date
    05-07-2007
    Location
    Singapore
    MS-Off Ver
    2006/2016
    Posts
    170

    Add 2 hours to Cell containing date/time

    I have this data in Cell A1

    Nov 7, 2020 11:26:50 PM PST

    I just need to add 2 hours to the time and remove the timezone so it looks like

    Nov 8, 2020 01:26:50 AM

    I was thinking of using bunch of MID and Search formulas to do so but there must be an easier way right?
    Last edited by lordfa9; 11-26-2020 at 11:22 PM.
    Reply With QuoteRegister To Reply
  2. 11-25-2020, 05:16 AM #2
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,780

    Re: Add 2 hours to Cell containing date/time

    How is that data produced?

    There are instructions at the top of the page explaining how to attach your sample workbook.

    To add two hours to a real date/time:

    =A1+(1/12)
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 11-25-2020, 05:25 AM #3
    bebo021999
    bebo021999 is offline
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,463

    Re: Add 2 hours to Cell containing date/time

    Assuming: day could be 1 or 2 digits (7 or 17&
    time is in "hh:mm:ss" with 8 digits
    the last is AM/PM & PST

    Try:

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Quang PT
    Reply With QuoteRegister To Reply
  4. 11-26-2020, 10:14 PM #4
    lordfa9
    lordfa9 is offline
    Forum Contributor
    Join Date
    05-07-2007
    Location
    Singapore
    MS-Off Ver
    2006/2016
    Posts
    170

    Re: Add 2 hours to Cell containing date/time

    adding a example file
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 11-26-2020, 10:15 PM #5
    lordfa9
    lordfa9 is offline
    Forum Contributor
    Join Date
    05-07-2007
    Location
    Singapore
    MS-Off Ver
    2006/2016
    Posts
    170

    Re: Add 2 hours to Cell containing date/time

    Quote Originally Posted by AliGW View Post
    How is that data produced?

    There are instructions at the top of the page explaining how to attach your sample workbook.

    To add two hours to a real date/time:

    =A1+(1/12)
    Quote Originally Posted by bebo021999 View Post
    Assuming: day could be 1 or 2 digits (7 or 17&
    time is in "hh:mm:ss" with 8 digits
    the last is AM/PM & PST

    Try:

    Please Login or Register  to view this content.
    neither method words sadly
    Reply With QuoteRegister To Reply
  6. 11-26-2020, 11:21 PM #6
    lordfa9
    lordfa9 is offline
    Forum Contributor
    Join Date
    05-07-2007
    Location
    Singapore
    MS-Off Ver
    2006/2016
    Posts
    170

    Re: Add 2 hours to Cell containing date/time

    LEFT(A2,LEN(A2)-4)+1/12

    Managed to come up with a solution, basically I use a LEFT and LEN to remove the PST, and added the 2 hours then
    Reply With QuoteRegister To Reply
  7. 11-26-2020, 11:34 PM #7
    bebo021999
    bebo021999 is offline
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,463

    Re: Add 2 hours to Cell containing date/time

    Try again, date could be "Nov 4, 2020 12:15:45 AM PST" (27 digits) or "Nov 4, 2020 2:15:45 AM PST" (26 digits) or full "Nov 24, 2020 12:34:46 AM PST" (28 digits)

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Timesheet that calcs time in, time out, lunch used, reg hours, ot hours, double hours
    By noobface in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 08-21-2021, 03:45 AM
  2. [SOLVED] Calculate Hours from Date & Time Entered to Date & Time today
    By Mr.Yoso in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-22-2020, 07:40 PM
  3. Calculates due date from a date based on working hours, start time and end time
    By icenature in forum Excel General
    Replies: 1
    Last Post: 11-11-2019, 10:04 AM
  4. Start Date + Hours to produce end date & Time using Business Hours (AND include weekend)
    By starduck in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-31-2016, 10:14 AM
  5. [SOLVED] Calculate number of hours from Start date and time and End date and time
    By sathyasun in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-28-2013, 01:04 AM
  6. [SOLVED] Want to add two hours to date/time cell
    By liebrechtf in forum Excel General
    Replies: 11
    Last Post: 06-11-2012, 04:38 AM
  7. Working out hours between date in a cell and the current time
    By MasonSinclair in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-19-2011, 06:06 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1