|
Need formula to subtract 5hrs from date/time field
I'm working with the following date column in my excel document but it is in
GMT. I need a formula to subtract 5hrs from the time stamp in Column "G" in order to convert it to EST. G CLOSE_DATE 01/08/2009 1:40:46 AM 01/08/2009 2:32:55 AM 01/08/2009 5:40:33 AM 01/08/2009 5:47:32 AM 01/08/2009 5:49:58 AM 01/08/2009 6:30:43 AM 01/08/2009 10:43:55 AM Any help would be greatly appreciated. |
Answer: Need formula to subtract 5hrs from date/time field
Sure, I can help you with that! You can use the following formula to subtract 5 hours from the time stamp in Column G:
This formula uses the TIME function to subtract 5 hours (represented as 5,0,0) from the time stamp in cell G2. You can then copy this formula down the column to apply it to all the cells in the column. Alternatively, you can use the following formula to subtract 5 hours from the entire date/time value in Column G:
This formula divides 5 by 24 (since there are 24 hours in a day) to convert 5 hours to a decimal value, and then subtracts that value from the date/time value in cell G2. Again, you can copy this formula down the column to apply it to all the cells in the column. |
Need formula to subtract 5hrs from date/time field
You just need to remember that times are stored internally in Excel as
fractions of a 24-hour day. Thus, to reduce a time by 5 hours you need to subtract 5/24 from the time. You can do this with the following formula: =G2-5/24 and then copy this down, assuming your date/times are in Excel format. You could then fix the values, and then copy/paste those to over-write the originals in column G. Another way is to enter this in a blank cell somewhe =5/24 then select that cell and click <copy. Move the cursor and highlight all those cells in column G with the date/time in, then click on Edit | Paste Special | Values (check) | Subtract (check) | OK then <Esc. Hope this helps. Pete On Jan 12, 5:37*pm, Ken wrote: I'm working with the following date column in my excel document but it is in GMT. I need a formula to subtract 5hrs from the time stamp in Column "G" in order to convert it to EST. * * * * * * * * G CLOSE_DATE 01/08/2009 *1:40:46 AM 01/08/2009 *2:32:55 AM 01/08/2009 *5:40:33 AM 01/08/2009 *5:47:32 AM 01/08/2009 *5:49:58 AM 01/08/2009 *6:30:43 AM 01/08/2009 *10:43:55 AM Any help would be greatly appreciated. |
Need formula to subtract 5hrs from date/time field
Ken
with your date/time in a1 use =A1-TIME(5,0,0) Mike "Ken" wrote: I'm working with the following date column in my excel document but it is in GMT. I need a formula to subtract 5hrs from the time stamp in Column "G" in order to convert it to EST. G CLOSE_DATE 01/08/2009 1:40:46 AM 01/08/2009 2:32:55 AM 01/08/2009 5:40:33 AM 01/08/2009 5:47:32 AM 01/08/2009 5:49:58 AM 01/08/2009 6:30:43 AM 01/08/2009 10:43:55 AM Any help would be greatly appreciated. |
Need formula to subtract 5hrs from date/time field
I cannot get the formula to work. It seems the formula gets confused because
there is a date value as well as a time value. I tried the following formula and it gave me the following result: Formula =(+TIME(5,0,0)) If I try to use =G2-TIME(5,0,0) it gives me a circular reference. Result 01/00/1900 5:00:00 AM instead of what I would like to see for G2 which is 01/08/2009 8:40:46 AM instead of 01/08/2009 1:40:46 AM Regards, "Mike H" wrote: Ken with your date/time in a1 use =A1-TIME(5,0,0) Mike "Ken" wrote: I'm working with the following date column in my excel document but it is in GMT. I need a formula to subtract 5hrs from the time stamp in Column "G" in order to convert it to EST. G CLOSE_DATE 01/08/2009 1:40:46 AM 01/08/2009 2:32:55 AM 01/08/2009 5:40:33 AM 01/08/2009 5:47:32 AM 01/08/2009 5:49:58 AM 01/08/2009 6:30:43 AM 01/08/2009 10:43:55 AM Any help would be greatly appreciated. |
Need formula to subtract 5hrs from date/time field
Hi Pete,
Your last option works but I need to automate it. Is there a way to execute the formula automatically when I input a new data? The report would be more user friendly if I didn't have to copy and paste special for column "G" everytime I input new data. Just wondering. Regards, "Pete_UK" wrote: You just need to remember that times are stored internally in Excel as fractions of a 24-hour day. Thus, to reduce a time by 5 hours you need to subtract 5/24 from the time. You can do this with the following formula: =G2-5/24 and then copy this down, assuming your date/times are in Excel format. You could then fix the values, and then copy/paste those to over-write the originals in column G. Another way is to enter this in a blank cell somewhe =5/24 then select that cell and click <copy. Move the cursor and highlight all those cells in column G with the date/time in, then click on Edit | Paste Special | Values (check) | Subtract (check) | OK then <Esc. Hope this helps. Pete On Jan 12, 5:37 pm, Ken wrote: I'm working with the following date column in my excel document but it is in GMT. I need a formula to subtract 5hrs from the time stamp in Column "G" in order to convert it to EST. G CLOSE_DATE 01/08/2009 1:40:46 AM 01/08/2009 2:32:55 AM 01/08/2009 5:40:33 AM 01/08/2009 5:47:32 AM 01/08/2009 5:49:58 AM 01/08/2009 6:30:43 AM 01/08/2009 10:43:55 AM Any help would be greatly appreciated. |
Need formula to subtract 5hrs from date/time field
I assumed you had these date/times already in a column and wanted to
change all of them as a one-off. I didn't realise you were typing them in yourself. Can't you just mentally subtract 5 hours as you type each one? The problem with trying to automate the process I described is that it applies to all the data in column G, whereas you would want it to apply to each cell in G as the data was entered. So, you need a slightly different process and an event macro which will automatically subtract those 5 hours for you from a new data entry in column G. I can't help with this, but perhaps someone else can ... Pete On Jan 12, 6:43*pm, Ken wrote: Hi Pete, Your last option works but I need to automate it. Is there a way to execute the formula automatically when I input a new data? The report would be more user friendly if I didn't have to copy and paste special for column "G" everytime I input new data. Just wondering. Regards, "Pete_UK" wrote: You just need to remember that times are stored internally in Excel as fractions of a 24-hour day. Thus, to reduce a time by 5 hours you need to subtract 5/24 from the time. You can do this with the following formula: =G2-5/24 and then copy this down, assuming your date/times are in Excel format. You could then fix the values, and then copy/paste those to over-write the originals in column G. Another way is to enter this in a blank cell somewhe =5/24 then select that cell and click <copy. Move the cursor and highlight all those cells in column G with the date/time in, then click on Edit | Paste Special | Values (check) | Subtract (check) | OK then <Esc. Hope this helps. Pete On Jan 12, 5:37 pm, Ken wrote: I'm working with the following date column in my excel document but it is in GMT. I need a formula to subtract 5hrs from the time stamp in Column "G" in order to convert it to EST. * * * * * * * * G CLOSE_DATE 01/08/2009 *1:40:46 AM 01/08/2009 *2:32:55 AM 01/08/2009 *5:40:33 AM 01/08/2009 *5:47:32 AM 01/08/2009 *5:49:58 AM 01/08/2009 *6:30:43 AM 01/08/2009 *10:43:55 AM Any help would be greatly appreciated.- Hide quoted text - - Show quoted text - |
Need formula to subtract 5hrs from date/time field
Use a different cell (directly to the right of the input cell???) to show the
adjusted date/time. Ken wrote: I cannot get the formula to work. It seems the formula gets confused because there is a date value as well as a time value. I tried the following formula and it gave me the following result: Formula =(+TIME(5,0,0)) If I try to use =G2-TIME(5,0,0) it gives me a circular reference. Result 01/00/1900 5:00:00 AM instead of what I would like to see for G2 which is 01/08/2009 8:40:46 AM instead of 01/08/2009 1:40:46 AM Regards, "Mike H" wrote: Ken with your date/time in a1 use =A1-TIME(5,0,0) Mike "Ken" wrote: I'm working with the following date column in my excel document but it is in GMT. I need a formula to subtract 5hrs from the time stamp in Column "G" in order to convert it to EST. G CLOSE_DATE 01/08/2009 1:40:46 AM 01/08/2009 2:32:55 AM 01/08/2009 5:40:33 AM 01/08/2009 5:47:32 AM 01/08/2009 5:49:58 AM 01/08/2009 6:30:43 AM 01/08/2009 10:43:55 AM Any help would be greatly appreciated. -- Dave Peterson |
Need formula to subtract 5hrs from date/time field
Thanks for your help Pete. I will create a macro to change all cells within
column G. I was just wonding if there was a formula that could remain in the cell so it would change new cell data entries on a daily basis. A macro will work with the formula you gave me. Thanks again. "Pete_UK" wrote: I assumed you had these date/times already in a column and wanted to change all of them as a one-off. I didn't realise you were typing them in yourself. Can't you just mentally subtract 5 hours as you type each one? The problem with trying to automate the process I described is that it applies to all the data in column G, whereas you would want it to apply to each cell in G as the data was entered. So, you need a slightly different process and an event macro which will automatically subtract those 5 hours for you from a new data entry in column G. I can't help with this, but perhaps someone else can ... Pete On Jan 12, 6:43 pm, Ken wrote: Hi Pete, Your last option works but I need to automate it. Is there a way to execute the formula automatically when I input a new data? The report would be more user friendly if I didn't have to copy and paste special for column "G" everytime I input new data. Just wondering. Regards, "Pete_UK" wrote: You just need to remember that times are stored internally in Excel as fractions of a 24-hour day. Thus, to reduce a time by 5 hours you need to subtract 5/24 from the time. You can do this with the following formula: =G2-5/24 and then copy this down, assuming your date/times are in Excel format. You could then fix the values, and then copy/paste those to over-write the originals in column G. Another way is to enter this in a blank cell somewhe =5/24 then select that cell and click <copy. Move the cursor and highlight all those cells in column G with the date/time in, then click on Edit | Paste Special | Values (check) | Subtract (check) | OK then <Esc. Hope this helps. Pete On Jan 12, 5:37 pm, Ken wrote: I'm working with the following date column in my excel document but it is in GMT. I need a formula to subtract 5hrs from the time stamp in Column "G" in order to convert it to EST. G CLOSE_DATE 01/08/2009 1:40:46 AM 01/08/2009 2:32:55 AM 01/08/2009 5:40:33 AM 01/08/2009 5:47:32 AM 01/08/2009 5:49:58 AM 01/08/2009 6:30:43 AM 01/08/2009 10:43:55 AM Any help would be greatly appreciated.- Hide quoted text - - Show quoted text - |
Need formula to subtract 5hrs from date/time field
Works like a charm Dave! I will add an additional column at the end of the
spreadsheet which will convert Column G into EST. This helps ... thanks! "Dave Peterson" wrote: Use a different cell (directly to the right of the input cell???) to show the adjusted date/time. Ken wrote: I cannot get the formula to work. It seems the formula gets confused because there is a date value as well as a time value. I tried the following formula and it gave me the following result: Formula =(+TIME(5,0,0)) If I try to use =G2-TIME(5,0,0) it gives me a circular reference. Result 01/00/1900 5:00:00 AM instead of what I would like to see for G2 which is 01/08/2009 8:40:46 AM instead of 01/08/2009 1:40:46 AM Regards, "Mike H" wrote: Ken with your date/time in a1 use =A1-TIME(5,0,0) Mike "Ken" wrote: I'm working with the following date column in my excel document but it is in GMT. I need a formula to subtract 5hrs from the time stamp in Column "G" in order to convert it to EST. G CLOSE_DATE 01/08/2009 1:40:46 AM 01/08/2009 2:32:55 AM 01/08/2009 5:40:33 AM 01/08/2009 5:47:32 AM 01/08/2009 5:49:58 AM 01/08/2009 6:30:43 AM 01/08/2009 10:43:55 AM Any help would be greatly appreciated. -- Dave Peterson |
| All times are GMT +1. The time now is 10:41 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com