formatting a date

sassriverrat

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

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

sassriverrat

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

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
655
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
655
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
655
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 ?
 

Forum statistics

Threads
1,144,103
Messages
5,722,490
Members
422,439
Latest member
zumbaZumba

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top