Solved

MS Excel CST MST and PST

Posted on 2014-04-24
10
308 Views
Last Modified: 2014-04-28
Hello,
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.
0
Comment
Question by:chima
  • 5
  • 4
10 Comments
 
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.
0
 
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.
0
 

Author Comment

by:chima
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.
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 32

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.

Thanks
Rob H
0
 
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.
0
 

Author Comment

by:chima
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
0
 
LVL 26

Expert Comment

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

Author Comment

by:chima
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
0
 
LVL 26

Accepted Solution

by:
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)
0
 

Author Closing Comment

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

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel Conditional Statements 11 40
VBA Help 18 46
Office 2016 Temp Files 3 30
How to transform one row line like this in excel table 2010? 22 23
No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
My experience with Windows 10 over a one year period and suggestions for smooth operation
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

832 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