Changing CST to EST - PLUS DATE CHANGE - Suggestions?

kescco

Board Regular
Joined
Sep 14, 2009
Messages
174
I know that placing 03:00:00 in a cell, copying it, and then Paste Special - Add will update the times.

My questions is how would you change the date in another column if the added three hours changes the date?

Date
Time CST
09-24-201422:00:00
09-25-201422:30:00

<tbody>
</tbody>






Converted to EST
Time Conversion is understood.
Any idea on how to convert date if time change changes the date?

DateTime EST
09-25-201401:00:00
09-26-201401:30:00

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,779
Office Version
  1. 2010
Platform
  1. Windows
Put the date and time in the same cell -- which you can do easily by copying the time column, Paste Special > Add to the date column, and then reformatting to show date and time.
 

kescco

Board Regular
Joined
Sep 14, 2009
Messages
174
Works very nicely. Another way if someone wants to use a formula might be something like:

IF(AND(B1 >= TIME(0,0,0), B1 < TIME(3,0,0)), SUM(DATEVALUE(C2),1), C2)

This is assuming the time is in Column B and the date in Column C.

Thank you for your help.
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,779
Office Version
  1. 2010
Platform
  1. Windows
You're welcome.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,687
Messages
5,597,541
Members
414,154
Latest member
thevaper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top