MS Excel CST MST and PST

Posted on 2014-04-24
Last Modified: 2014-04-28
I am embarrassed to ask this question, yet after trying to find the solution, I thought it be best to ask.  The problem is very simple.  Using Excel, I want to have three columns; CST, MST, and PST.  The solution is that MST will be the reference time (Mountain Std Time) in military time (0-24), CST is Central Std Time and PST is Pacific Std Time.
Therefore the center column will be MST, the column to the left will be CST=MST+1 hour.
And PST=MST - 1 hour.  I do not need the date.  I played with TIME() and TIMEVALUE() and "without going off and reading about it" I could not get it to work.
Question by:chima
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
LVL 26

Expert Comment

by:Shaun Kline
ID: 40021264
Though you may not want the date, it may be easier having it and just formatting your cells to hide that date portion. In this way, you are not adding/subtracting 1, but rather adding/subtracting 1/24.
LVL 26

Expert Comment

by:Shaun Kline
ID: 40021269
The rational is that Excel stores dates as decimals, where 12am is the start of a day (or rather the whole number), and the hours, minutes and seconds are parts of a day.

Author Comment

ID: 40021544
Kline, lol, that is well and good, but how can I do it?  I'm now going to try by keeping the date.
Create Professional Looking Email Signatures

Create "Professional HTML Email Signatures" with ease.
7 Day Money Back Guarantee if not 100% Satisfied.
Affordable - Try it out for 7 Days Totally Risk Free.
Installers provided for over 45 Email clients.
Both Windows & MAC Supported.
Highly Recommended!

LVL 33

Assisted Solution

by:Rob Henson
Rob Henson earned 200 total points
ID: 40022196
By Military time, do you mean standard 24 hour clock? Will it have the colon to show the hours and minutes split eg 15:30 for 3:30 pm.

If your times are formatted in that fashion then you don't need to worry about the date portion, Excel will deal with it.

Just tried on a blank sheet with 23:30 in cell D7. In the cell to the right I put
=D7+1/24 and it gives 00:30
In the cell to the left I put
=D7-1/24 and it gives 22:30

Both results as expected. The 1/24 section is because time is dealt with as a portion of a day with days being integer values and time decimal portions thereof; one day = 24 hours therefore 1 hour = 1/24.

Rob H
LVL 26

Expert Comment

by:Shaun Kline
ID: 40022443
By hiding the date, I mean setting the cell format to only display the time portion. If you select your cells, change the format drop-down to Custom and select the format that matches your needs. For military time, this would be h:mm;@. If you want to show a zero for hours less that 10, enter a format of hh:mm;@.
You can also reach the format window by right clicking the selected cells hand choosing "Format Cells..." from the popup menu.

Author Comment

ID: 40023057
robhenson, thanks, your solution is correct, now as simple as it might look, how does one add one hour to the MST column?  Again I tried what I thought might work, but no deal.
While I know that CST is one hour more, and PST is one hour less, and having this on Excel for the 24 hours for MST, this has turned into a lesson on "how to do it."
The -/+ of 1/24 works;
using 1 divided by 24
LVL 26

Expert Comment

by:Shaun Kline
ID: 40023142
TimeValue takes a string. Use it like this: TIMEVALUE("1:00:00").

Author Comment

ID: 40023256
Kline, thanks, closer now.  Why aren't the MST times/value being shown?  The value is correct because of the values in columns I and K.  I made the first value in PST ABS() because it is negative and was not being displayed;
timevalue string
When I change AM to PM, then the first time/value in column J is displayed as 12:00
LVL 26

Accepted Solution

Shaun Kline earned 300 total points
ID: 40023460
The format being displayed in J3 down appears to be mm:ss.0 instead of hh:mm:ss.

As for the ABS, that may give you incorrect times. Try using this formula:
=IF(J2<1/24, J2+1-1/24, J2-1/24)

Author Closing Comment

ID: 40027310
Thanks, appreciate the help.  I did not have the time to dive more into the solution.

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question