Solved

MS Excel CST MST and PST

Posted on 2014-04-24
10
272 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
 
LVL 31

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
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.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

705 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now