Is there a way to format date-time with proper alignment?

[JenniferMurphy]

I would like to be able to format timestamps (date + time) so that both align but without leading zeroes for either. I do not see an easy way to do this. I will provide both a screenshot and a minisheet because the minisheets do not always preserve the look of the actual sheet, although this time it is pretty good.

• Column B has the time stamp formatted as "m/dd/yy h:mm AM/PM". The alignment of the dates is off.
• In Column C, I made the time field 9 characters regardless of the time, but apparently, spaces are not as wide as numbers in variable-spaced fonts,
• Column D uses a mono-spaced font. The alignment works, but has a different look.
• Column E solves the problem by changing to 24-hour time. The alignment works, but has a different look.
• Column F uses a Rube Goldberg solution that seems to work because the space character appears to be half as wide as the number characters.

Is there a better way?

Here's the minisheet.

Medical Logs.xlsx
	B	C	D	E	F
2	B	C	D	E	F
3	Date & Time	Format Try	Formatted Mono	Formatted 24 hr	2 spaces = 1
4	12/12/23 10:30 PM	12/12/23 10:30 PM	12/12/23 10:30 PM	12/12/23 22:30	12/12/23 10:30 PM
5	2/03/24 6:03 AM	2/03/24 6:03 AM	2/03/24 6:03 AM	2/03/24 06:03	2/03/24 6:03 AM
6	3/28/24 11:44 AM	3/28/24 11:44 AM	3/28/24 11:44 AM	3/28/24 11:44	3/28/24 11:44 AM
MrExcel

Cell Formulas
Range	Formula
C4:D6	C4	=TEXT([@[Date & Time]],"m/dd/yy") & RIGHT(" " & TEXT([@[Date & Time]],"h:mm am/pm"),9)
E4:E6	E4	=[@[Date & Time]]
F4:F6	F4	=LET(time, TEXT([@[Date & Time]],"h:mm am/pm"), TEXT([@[Date & Time]],"m/dd/yy") & LEFT(" ",2*(9-LEN(time))) & time)

 

[Tetra201]

Half of what you want: mm/dd/yy * h:mm AM/PM

 

[Jeffrey Mahoney]

I created a Conditional Formatting with this formula: =HOUR(F1)<10 and with this formatting: m/d/yy __ h:mm AM/PM

The standard number formatting I applied was: m/d/yy h:mm AM/PM

 

[JenniferMurphy]

Half of what you want: mm/dd/yy * h:mm AM/PM

Half of what you want: mm/dd/yy * h:mm AM/PM

Interesting. I had never used "*" in a format before. But, as you say, it's only a half solution. It only works with "mm" for the months.

 

[JenniferMurphy]

I created a Conditional Formatting with this formula: =HOUR(F1)<10 and with this formatting: m/d/yy __ h:mm AM/PM

The standard number formatting I applied was: m/d/yy h:mm AM/PM

I couldn't get this to work. Here's how it looks. It's in Column G:

Here's the mnisheet:

Medical Logs.xlsx
	B	C	D	E	F	G	H
2	B	C	D	E	F	G	H
3	Date & Time	Format Try	Formatted Mono	Formatted 24 hr	2 spaces = 1	* Formatting	Conditional
4	12/12/23 10:30 PM	12/12/23 10:30 PM	12/12/23 10:30 PM	12/12/23 22:30	12/12/23 10:30 PM	12/12/23 10:30 PM	12/12/23 10:30 PM
5	2/03/24 6:03 AM	2/03/24 6:03 AM	2/03/24 6:03 AM	2/03/24 06:03	2/03/24 6:03 AM	2/03/24 6:03 AM	2/3/24 6:03 AM
6	3/28/24 11:44 AM	3/28/24 11:44 AM	3/28/24 11:44 AM	3/28/24 11:44	3/28/24 11:44 AM	3/28/24 11:44 AM	3/28/24 11:44 AM
MrExcel

Cell Formulas
Range	Formula
C4:D6	C4	=TEXT([@[Date & Time]],"m/dd/yy") & RIGHT(" " & TEXT([@[Date & Time]],"h:mm am/pm"),9)
E4:E6,G4:H6	E4	=[@[Date & Time]]
F4:F6	F4	=LET(time, TEXT([@[Date & Time]],"h:mm am/pm"), TEXT([@[Date & Time]],"m/dd/yy") & LEFT(" ",2*(9-LEN(time))) & time)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H4:H6	Expression	=HOUR($H$4)<10	text	NO

And this is the applied formatting:

 

[Phuoc]

Try this

Book1 (3).xlsx
	B	C
1
2	B	C
3	Date & Time	Conditional
4	12/12/2023 10:30 PM	12/12/2023 10:30 PM
5	2/3/2024 6:03 AM	2/03/2024 6:03 AM
6	3/2/2024 11:44 AM	3/02/2024 11:44 AM
7	3/29/2024 7:30 AM	3/29/2024 7:30 AM
8
9	MOD(HOUR(C4),12)<10	m/dd/yyyy" " h:mm AM/PM
Sheet2

Cell Formulas
Range	Formula
C4:C7	C4	=B4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C4:C7	Expression	=MOD(HOUR(C4),12)<10	text	NO

 

[JenniferMurphy]

Try this

That works! Nice!

Medical Logs.xlsx
	B							I	J
2	B							I	J
3	Date & Time							Mod	Conditonal2
4	12/12/23 10:30 PM							FALSE	12/12/23 10:30 PM
5	2/03/24 6:03 AM							TRUE	2/03/24 6:03 AM
6	3/28/24 11:44 AM							FALSE	3/28/24 11:44 AM
MrExcel

Cell Formulas
Range	Formula
I4:I6	I4	=MOD(HOUR([@[Date & Time]]),12)<10
J4:J6	J4	=[@[Date & Time]]

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J4:J6	Expression	=MOD(HOUR($B4),12)<10	text	NO

Thank you

 

[JenniferMurphy]

Ok. I've done some testing and I think I have gotten the various solutions accurately represented below.

Date & Time.xlsx
	B	C	D	E	F	G	H	I	J
3	Standard	24 hour	Conditonal 1	Cond 1	Conditional 2	Cond 2	*	Date	Time
4	2/03/23 6:03 AM	2/03/23 06:03	2/03/23 6:03 AM	TRUE	2/03/23 6:03 AM	TRUE	2/03/23 6:03 AM	2/03/23	6:03:00 AM
5	3/28/23 11:44 PM	3/28/23 23:44	3/28/23 11:44 PM	FALSE	3/28/23 11:44 PM	FALSE	3/28/23 11:44 PM	3/28/23	11:44:00 PM
6	4/01/23 9:59 AM	4/01/23 09:59	4/01/23 9:59 AM	TRUE	4/01/23 9:59 AM	TRUE	4/01/23 9:59 AM	4/01/23	9:59:00 AM
7	5/05/23 10:00 AM	5/05/23 10:00	5/05/23 10:00 AM	FALSE	5/05/23 10:00 AM	FALSE	5/05/23 10:00 AM	5/05/23	10:00:00 AM
8	9/05/23 6:00 AM	9/05/23 06:00	9/05/23 6:00 AM	TRUE	9/05/23 6:00 AM	TRUE	9/05/23 6:00 AM	9/05/23	6:00:00 AM
9	10/05/23 6:00 AM	10/05/23 06:00	10/05/23 6:00 AM	TRUE	10/05/23 6:00 AM	TRUE	10/05/23 6:00 AM	10/05/23	6:00:00 AM
10	11/01/23 11:55 PM	11/01/23 23:55	11/01/23 11:55 PM	FALSE	11/01/23 11:55 PM	FALSE	11/01/23 11:55 PM	11/01/23	11:55:00 PM
D&T Align Same Cell

Cell Formulas
Range	Formula
C4:D10,F4:F10,H4:J10	C4	=[@Standard]
E4:E10	E4	=MOD(HOUR([@[Conditonal 1]]),12)<10
G4:G10	G4	=HOUR([@[Conditional 2]])<10

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F4:F10	Expression	=HOUR($F4)<10	text	NO
D4:D10	Expression	=MOD(HOUR($D4),12)<10	text	NO

This table provides some explanation.

Date & Time.xlsx
	L	M	N	O	P	Q
12	Column	Quality	Format [Conditional Format]	Condition	Font	Comments
13	Standard	Poor	m/dd/yy h:mm AM/PM	n/a	Any
14	24 hour	Ok	m/dd/yy hh:mm	n/a	Any	Good if 24-hour format is Ok
15	Conditonal 1	Good	m/dd/yy h:mm AM/PM m/dd/yy" " h:mm AM/PM	=MOD(HOUR($D4),12)<10	Any	Add 2 spaces if 1-digit hour
16	Conditional 2	Good	m/dd/yy_0h:mm AM/PM m/dd/yy_0_0h:mm AM/PM	=HOUR($F4)<10	Any	Add 1 digit-space if 1-digit hour
17	*	Poor	m/dd/yy * h:mm AM/PM	n/a	Any
18	Date	Good	m/dd/yy	n/a	Any	Date in separate column
19	Time	Good	h:mm:ss	n/a	Any	Time in separate column
D&T Align Same Cell

Cell Formulas
Range	Formula
L13	L13	=TblDT01[[#Headers],[Standard]]
L14	L14	=TblDT01[[#Headers],[24 hour]]
L15	L15	=TblDT01[[#Headers],[Conditonal 1]]
L16	L16	=TblDT01[[#Headers],[Conditional 2]]
L17	L17	=TblDT01[[#Headers],[*]]
L18	L18	=TblDT01[[#Headers],[Date]]
L19	L19	=TblDT01[[#Headers],[Time]]

The conditional solutions are too flakey. If I move a column, they can get screwed up. I'll probably either go with the 24-hour format or putting the time in a separate column.

Thanks for all the help.

 

[JenniferMurphy]

After a bit more testing, I decided to split the solutions into 2 separate sheets: one for formatting the timestamp in its own cell and one for formatting it somewhere else. The mini-sheets are below. Each sheet has two tables. One has the various solutions. The other has information and comments on those solutions. The solution tables are followed by a second table showing that the timestamp data is still valid by adding 1 day.

If anyone sees any errors or knows of any different solutions, please let me know.

I create these example workbooks for my own use so I don't have to go through the learning curve again in the future. I post them here in case they might be of use to anyone else.

Here is the Solution Table from the Format in the Same Cell sheet.

Date & Time.xlsx
	B	C	D	E	F	G	H	I	J
3	Standard	24 hour	Conditional 1	Cond 1	Conditional 2	Cond 2	* Format	Date	Time
4	2/03/23 6:03 AM	2/03/23 06:03	2/03/23 6:03 AM	TRUE	2/03/23 6:03 AM	TRUE	2/03/23 6:03 AM	2/03/23	6:03:00 AM
5	3/28/23 11:44 PM	3/28/23 23:44	3/28/23 11:44 PM	FALSE	3/28/23 11:44 PM	FALSE	3/28/23 11:44 PM	3/28/23	11:44:00 PM
6	4/01/23 9:59 AM	4/01/23 09:59	4/01/23 9:59 AM	TRUE	4/01/23 9:59 AM	TRUE	4/01/23 9:59 AM	4/01/23	9:59:00 AM
7	5/05/23 10:00 AM	5/05/23 10:00	5/05/23 10:00 AM	FALSE	5/05/23 10:00 AM	FALSE	5/05/23 10:00 AM	5/05/23	10:00:00 AM
8	9/05/23 6:00 AM	9/05/23 06:00	9/05/23 6:00 AM	TRUE	9/05/23 6:00 AM	TRUE	9/05/23 6:00 AM	9/05/23	6:00:00 AM
9	10/05/23 6:00 AM	10/05/23 06:00	10/05/23 6:00 AM	TRUE	10/05/23 6:00 AM	TRUE	10/05/23 6:00 AM	10/05/23	6:00:00 AM
10	11/01/23 11:55 PM	11/01/23 23:55	11/01/23 11:55 PM	FALSE	11/01/23 11:55 PM	FALSE	11/01/23 11:55 PM	11/01/23	11:55:00 PM
11
12	Standard +1 Day	24 hour +1 Day	Conditional 1 +1 Day	Cond 1	Conditional 2 +1 Day	Cond 2	* Format +1 Day	Date +1 Day	Time +1 Hour
13	2/04/23 6:03 AM	2/04/23 06:03	2/04/23 6:03 AM	n/a	2/04/23 6:03 AM	n/a	2/04/23 6:03 AM	2/04/23	7:03:00 AM
D&T Align Same Cell

Cell Formulas
Range	Formula
C4:D10,F4:F10,H4:J10	C4	=[@Standard]
E4:E10	E4	=MOD(HOUR([@[Conditional 1]]),12)<10
G4:G10	G4	=HOUR([@[Conditional 2]])<10
B13:D13,H13:I13,F13	B13	=B4+1
J13	J13	=J4+1/24

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F13	Expression	=HOUR($F13)<10	text	NO
D13	Expression	=MOD(HOUR($D13),12)<10	text	NO
F4:F10	Expression	=HOUR($F4)<10	text	NO
D4:D10	Expression	=MOD(HOUR($D4),12)<10	text	NO

And this is its Comment Table.

Date & Time.xlsx
	L	M	N	O	P	Q
12	Column	Quality	Format [Conditional Format]	Condition	Font	Comments
13	Standard	Poor	m/dd/yy h:mm AM/PM	n/a	Any	Source data
14	24 hour	Ok	m/dd/yy hh:mm	n/a	Any	Good if 24-hour format is Ok
15	Conditional 1	Good	m/dd/yy h:mm AM/PM m/dd/yy" " h:mm AM/PM	=MOD(HOUR($D4),12)<10	Any	Add 2 spaces if 1-digit hour
16	Conditional 2	Good	m/dd/yy_0h:mm AM/PM m/dd/yy_0_0h:mm AM/PM	=HOUR($F4)<10	Any	Add 1 digit-space if 1-digit hour
17	* Format	Poor	m/dd/yy * h:mm AM/PM	n/a	Any
18	Date	Excellent	m/dd/yy	n/a	Any	Date in separate column
19	Time	Excellent	h:mm:ss AM/PM	n/a	Any	Time in separate column
D&T Align Same Cell

Cell Formulas
Range	Formula
L13	L13	=TblDT01[[#Headers],[Standard]]
L14	L14	=TblDT01[[#Headers],[24 hour]]
L15	L15	=TblDT01[[#Headers],[Conditional 1]]
L16	L16	=TblDT01[[#Headers],[Conditional 2]]
L17	L17	=TblDT01[[#Headers],[* Format]]
L18	L18	=TblDT01[[#Headers],[Date]]
L19	L19	=TblDT01[[#Headers],[Time]]
N13	N13	=GetFormat(TblDT01[Standard])
N14	N14	=GetFormat(TblDT01[24 hour])
N15	N15	=GetFormat(TblDT01[Conditional 1])&" m/dd/yy"" "" h:mm AM/PM"
N16	N16	=GetFormat(TblDT01[Conditional 2])&" m/dd/yy_0_0h:mm AM/PM"
N17	N17	=GetFormat(TblDT01[* Format])
N18	N18	=GetFormat(TblDT01[Date])
N19	N19	=GetFormat(TblDT01[Time])

Here is the Solution Table from the Format in a Different Cell sheet.

Date & Time.xlsx
	B	C	D	E	F
3	Date & Time	Mono Text	24 hr	2 spaces = 1	Text
4	12/12/23 10:30 PM	12/12/23 10:30 PM	12/12/23 22:30	12/12/23 10:30 PM	12/12/23 10:30 PM
5	2/03/24 6:03 AM	2/03/24 6:03 AM	2/03/24 06:03	2/03/24 6:03 AM	2/03/24 6:03 AM
6	3/28/24 11:44 PM	3/28/24 11:44 PM	3/28/24 23:44	3/28/24 11:44 PM	3/28/24 11:44 PM
7	4/01/24 9:59 AM	4/01/24 9:59 AM	4/01/24 09:59	4/01/24 9:59 AM	4/01/24 9:59 AM
8	5/05/24 10:00 AM	5/05/24 10:00 AM	5/05/24 10:00	5/05/24 10:00 AM	5/05/24 10:00 AM
9	6/22/24 9:59 PM	6/22/24 9:59 PM	6/22/24 21:59	6/22/24 9:59 PM	6/22/24 9:59 PM
10
11	Date & Time +1 Day	Mono Text +1 Day	24 hr +1 Day	2 spaces = 1 +1 Day	Text +1 Day
12	12/13/23 10:30 PM	12/13/23 10:30 PM	12/13/23 22:30	12/13/23 10:30 PM	12/13/23 10:30 PM
D&T Align Other Cell

Cell Formulas
Range	Formula
C4:C9	C4	=TEXT([@[Date & Time]],"m/dd/yy") & RIGHT(" " & TEXT([@[Date & Time]],"h:mm am/pm"),9)
D4:D9	D4	=[@[Date & Time]]
E4:E9	E4	=LET(time, TEXT([@[Date & Time]],"h:mm am/pm"), TEXT([@[Date & Time]],"m/dd/yy") & LEFT(" ",2*(9-LEN(time))) & time)
F4:F9	F4	=TEXT([@[Date & Time]],"m/dd/yy") & RIGHT(" " & TEXT([@[Date & Time]],"h:mm am/pm"),10)
B12:F12	B12	=B4+1

And this is its Comments Table.

Date & Time.xlsx
	H	I	J	K	L
3	Column	Quality	Format	Font	Comments
4	Date & Time	Poor	m/dd/yy h:mm AM/PM	Proportional	Source data
5	Mono Text	Ok	m/dd/yy h:mm a/p	Monospaced	Much wider
6	24 hr	Ok	m/dd/yy hh:mm	Proportional	perfect alignment
7	2 spaces = 1	Good	General	Proportional	Complex formula
8	Text	Poor	m/dd/yy h:mm a/p	Proportional	I couldn't get this to work
D&T Align Other Cell

Cell Formulas
Range	Formula
H4	H4	=TblDT02[[#Headers],[Date & Time]]
H5	H5	=TblDT02[[#Headers],[Mono Text]]
H6	H6	=TblDT02[[#Headers],[24 hr]]
H7	H7	=TblDT02[[#Headers],[2 spaces = 1]]
H8	H8	=TblDT02[[#Headers],[Text]]
J4	J4	=GetFormat(TblDT02[Date & Time])
J5	J5	=GetFormat(TblDT02[Mono Text])
J6	J6	=GetFormat(TblDT02[24 hr])
J7	J7	=GetFormat(TblDT02[2 spaces = 1])
J8	J8	=GetFormat(TblDT02[Text])