formatting a date

sassriverrat

Well-known Member
Joined
Oct 4, 2018
Messages
620
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!
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

sassriverrat

Well-known Member
Joined
Oct 4, 2018
Messages
620
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

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
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

Well-known Member
Joined
Oct 4, 2018
Messages
620

ADVERTISEMENT

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:
B/DDHHMMZ MMM//
C/DDMMN(LAT)/DDDMMW(LONG)

<colgroup><col></colgroup><tbody>
</tbody>


And this is what I have:
Code:
="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

Well-known Member
Joined
Oct 4, 2018
Messages
620
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
Code:
=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
 
Last edited:

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680

ADVERTISEMENT

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

Does it mean 12 degrees 34 minutes et 5 seconds ?
 

sassriverrat

Well-known Member
Joined
Oct 4, 2018
Messages
620
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:

Code:
="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

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
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

Well-known Member
Joined
Oct 4, 2018
Messages
620
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 ?
 

Watch MrExcel Video

Forum statistics

Threads
1,108,523
Messages
5,523,370
Members
409,514
Latest member
MarkZuckerberg

This Week's Hot Topics

Top