formatting a date

[sassriverrat]

Good Morning,

I have four cells with time/date info and Time zone adjustment as follows:

A1: Date formatted dd-mmm-yy
A2: Time formatted hh:mm (24hr) (local time)
A3: + or - symbol to denote zone description
A4: whole number (i.e. 5) to denote hours from UTC or GMT.

So it would appear, looking at A3 and A4, that if A3 is - and A4 is 5, then the time is -5 or 5 hours behind GMT.

Ok, I'm trying to get all of that info in one cell and formatted as:
ddhhmmz MMM

where dd is day-day-hour-hour-min-min-"zulu" Month-month-month

Does someone know how to do this?

Thanks!

 

[James006]

You are welcome ...

 

[sassriverrat]

any ideas on fixing my "temporary" idea for the time. I have a few ideas, just haven't tried experimenting with any of them yet.

 

[Rick Rothstein]

If my understanding is correct ..."12-34.5" to "01235"

=TEXT(LEFT(A1,FIND("-",A1)-1),"000")&TEXT(ROUND(VALUE(SUBSTITUTE(A1,LEFT(A1,FIND("-",A1)),"")),0),"00")

Hope this will help

If your formula is works for the OP, then I think this one will also...

=TEXT(SUBSTITUTE(A1,"-",""),"00000")

 

[Rick Rothstein]

any ideas on fixing my "temporary" idea for the time. I have a few ideas, just haven't tried experimenting with any of them yet.

I think this formula will do what you want...

=UPPER(TEXT(D4+F4-SIGN(B5&1)*C5/24,"\B\/ddhhmm\Z mmm\/\/"))

 

[Tetra201]

To match the output of the "temporary" formula from Post # 6, there should be only one "d". Also, the SIGN function can be removed.

=UPPER(TEXT(F4+D4-(B5&C5)/24,"\B\/dhhmmZ mmm\/\/"))

 

[sassriverrat]

Gentleman, both seemed to work perfectly. Again, Kudos!

However, I have to ask:

1. What are the forward and backslashes next to each other doing? I don't think I've seen that before.

2. What is the difference in these (in terms of what they are doing)? They both seem to work perfectly...but just plugging in a few numbers to test.

 

[Rick Rothstein]

1. What are the forward and backslashes next to each other doing? I don't think I've seen that before.

The key to the TEXT function (and cell formatting) is that in the pattern string certain characters have special meaning (for example, the "d" standing for the day number in a date as opposed to being just the letter "d")... the backslash takes away any special meaning the next character following it may have and tells the pattern string to just use the character as a text character without special meaning (so that the "d" is interpreted as just the letter "d" and not a special character meaning day number). It is equivalent to surrounding a character with quote marks within the pattern text string.

2. What is the difference in these (in terms of what they are doing)? They both seem to work perfectly...but just plugging in a few numbers to test.

Both Tetra201 and my formulas are, for all practical purposes, identical. Tetra201 tightened up (compacted) the formula I posted by changing the multiplication of SIGN(B5&1)*C5 that I used to (B5&C5) thus eliminating a function call. He also corrected an apparent mistake I made by specifying a two-digit day number (leading zero when the day number is less than 10) when your temporary formula specified single digits to be used when the day number is less than 10.

 

[sassriverrat]

Thank you very much for that description. Ok- that makes a lot of sense. Love learning something new!

I realize that 500 or so posts ago I knew almost nothing (at least compared to what I know now...) about excel and now 18 months later, I know a LOT more about excel (all of my VBA was learned on here with devoted members' help) and the workbook my colleagues and I use at work is pretty awesome (compared to the archaic manual typing of EVERYTHING we used to do).

Thank you.

As for the formulas- I see now. I threw in OCT 01 into the input cells and saw the difference. Since I need the "01" I'll be using the appropriate formula. Thank you both again!

 

[sassriverrat]

so ONE more set of questions on these, promise.

1. With the longitude- I want to make sure that a user inputting a longitude, say "078-51.2" can't put in "78-51.2", basically forcing the zero to be added in front. How would I go about doing that? I figure this is probably a super simple question.

2. Based on #1 , I have another cell that inputs the "course" and was kind of wondering the same thing, only difference being that they could put in "1" and it should be "001" in the receiving cell. The receiving cell's formula is just

=UPPER("E/"&R10&"//")

 

[sassriverrat]

I think this formula will do what you want...

=UPPER(TEXT(D4+F4-SIGN(B5&1)*C5/24,"\B\/ddhhmm\Z mmm\/\/"))

So one last pair of questions:
1. I'm trying to modify your above formula for use within another formula. Never having used formatting like this, I quickly discovered that deleting the "B" didn't work...so how might I go about modifying this such that the "B" isn't there and there isn't a space there (because I found I could insert a space there and it worked).

2. Different material but along the same train of though, if I have two cells let's say, A1 and A2. Anything I type into A1 is going to be put in A2 so =A1. However, I want A2 to only accept have answers from 0-359 and the outcome must always be equal to three digits. So 1 inputted is shown as 001 and 90 input is shown as 090.
For the 0-359 piece, I wasn't sure if that should be an input rule on A1 or applied within the formula of A2. It only matters, for purposes of the spreadsheet, what's seen in A2

Thanks again

Edit: Figured out #1 so I'll start a new post on number 2. I just went with this on #1

 =UPPER(TEXT(D4+F4-SIGN(B5&1)*C5/24,"\/ddhhmm\Z mmm\/\/"))