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]

Hello,

Hope following link will help :

https://www.mrexcel.com/forum/excel-questions/168063-converting-zulu-local-time.html#post821627

 

[sassriverrat]

Unfortunately no. I appreciate the offer. I already have the time in GMT, but it's more of a matter of how to combine a bunch of cells into the same cell.

 

[James006]

For sure you know about concatenation ....

=A1&A2&A3&A4

Obviously whenever you are dealing with Values, you need to the TEXT function ...

Hope this will help

 

[sassriverrat]

Yessir

Am familiar with concatenation. Seem to be having a brain fart.

So here's what I've got and here's where I'm going-
Trying to figure out how to get three different things figured out-

1. How to take a time that formatted as HH:MM (in 24hr) and I want to either read the minutes out of it for another cell as MM (must have two digits even if 05 minutes, let's say) but can't "format" cell OR be able to read entire time as hhmm (no colon)

2. How to change this reading =Month(A1) where that equals, say "10" to equaling "OCT" (where A1 is a date)

3. How to get excel to take a latitude, formatted as 12-34.5 and rewrite it in the cell as 1235 (notice it rounded the 4 up to 5 in this case, and if the 5 had been a four, it would have just been 1234) but again, this has to be within the cell.


This is what I'm trying to accomplish:
[TABLE="width: 96"]
<colgroup><col></colgroup><tbody>[TR]
[TD]B/DDHHMMZ MMM//[/TD]
[/TR]
[TR]
[TD]C/DDMMN(LAT)/DDDMMW(LONG)[/TD]
[/TR]
</tbody>[/TABLE]


And this is what I have:

="B/"&DAY(F4)&(HOUR(IF(B5="-",(D4+(C5/24)),(D4-(C5/24)))))&(MINUTE(IF(B5="-",(D4+(C5/24)),(D4-(C5/24)))))&"Z "&MONTH(F4)&"//"

 

[sassriverrat]

solved # 1 and 2, so just really need to figure out #3

My solution for the #1 is temporary- Here's why

If I use the formula

=UPPER("B/"&DAY(F4)&(TEXT(IF(B5="-",(D4+(C5/24)),(D4-(C5/24))),"hhmm")&"Z "&(TEXT(DATE(2000,MONTH(F4),1),"mmm"))&"//"))

where
F4 is the date, formatted dd-MMM-YY
D4 is the time, formatted HH:mm
B5 is a "-" or "+" denoting time zone description
C5 is the time zone description
(so -5 in B5 and C5 denotes 5 hours behind UTC).

My formula works temporarily for my purposes, and correctly takes the time to the correct UTC time ("Z"), but it doesn't work if the date needs to roll forward or back a day....

Hopefully that makes sense
Thanks

 

[James006]

Regarding your latitude, formatted as 12-34.5 ...

Does it mean 12 degrees 34 minutes et 5 seconds ?

 

[sassriverrat]

Regarding your latitude, formatted as 12-34.5 ...

Does it mean 12 degrees 34 minutes et 5 seconds ?

Yes it does. The user types these in as "12-34.5" and then the next cell over is "N" or "S", etc.

However, for the report being generated in another cell, the latitude needs to be rounded off, in this case, as "1235N" and there's other stuff to go in the same cell, so I was going to do:

="C/"&(Lat from A1)&A2&"/"(Long from B1)&B2&"//"

Where A1 is the latitude (how to format though....?)
A2 is the "N" or "S"
B1 is the longitude (How to format as 07812 where that's 078-12.3 and the "0" in front of 078 MUST be there)
B2 is "W" or "E"

Thanks....figured this one might be fun!

 

[James006]

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

 

[sassriverrat]

You and a few others never cease to amaze. I’ll plug it in at lunch and give it a try but from the looks of it that should work ?