?
Solved

MS Excel CST MST and PST

Posted on 2014-04-24
10
Medium Priority
?
565 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 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 34

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

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

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

In this post, I will showcase the steps for how to create groups in Office 365. Office 365 groups allow for ease of flexibility and collaboration between staff members.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

616 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