Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MS Excel CST MST and PST

Posted on 2014-04-24
10
Medium Priority
?
477 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
[X]
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
10 Comments
 
LVL 27

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 27

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 33

Assisted Solution

by:Rob Henson
Rob Henson earned 800 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 27

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 27

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 27

Accepted Solution

by:
Shaun Kline earned 1200 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
New style of hardware planning for Microsoft Exchange server.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

722 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