VBA - Date vs Time

[hwelshim]

I receive a lot of reports that generate the date above the time. It then lists lots of times for logging in and logging out. Then it will list the next date followed by lots of times. When I select the cell with times, the date isn't present. I need to sort by date. I'm thinking there's an if statement I can write that recognizes the date and then inserts the date in a column next to the time column. I've tried ISDATE, but it recognizes time as TRUE. Any other ideas? Thank you all a lot!

 

[aRandomHelper]

Maybe this?

Book1.xlsm
	A	B
1	Date	Time
2	1/22/2021	1/22/2021
3	1/22/2021	14:02:24
4	1/22/2021	3:23:59
5	1/22/2021	6:29:52
6	1/22/2021	7:12:45
7	1/23/2021	1/23/2021
8	1/23/2021	9:44:51
9	1/23/2021	17:56:17
Sheet2

Cell Formulas
Range	Formula
A2:A9	A2	=IF(ISERROR(INT(B2)),B2,A1)

 

[hwelshim]

Hi *aRandomHelper, this looks super promising. The problem is when I input into my sheet, it triggers the error every time so I have "Date" going all the way down column A. Here's a weird thing though: when I delete the 1 from 2021, it works. It lists 1/22/202 and 1/23/202, but as soon as I make a proper date again, triggers the error. Any ideas?

 

[aRandomHelper]

I don't quite get it, can you show what you have so far with XL2BB?

 

[Dave Patton]

Date and Time.xlsm
	A	B
1	Date	Time
2	28-Jan-21	28-Jan-21
3	28-Jan-21 12:00	12:00
4	29-Jan-21 03:00	15:00
5	29-Jan-21 00:00	29-Jan-21
6	29-Jan-21 15:00	15:00
7	30-Jan-21 11:00	20:00
19c

Cell Formulas
Range	Formula
A2	A2	=B2
A3:A7	A3	=IF(B3<1,A2+B3,B3)

 

[Dave Patton]

Technical a better construction is as follows:

Date and Time.xlsm
	A	B	C
1		Time	Date+Time
2		28-Jan-21	28-Jan-21
3		12:00	28-Jan-21 12:00
4		15:00	29-Jan-21 03:00
5		29-Jan-21	29-Jan-21 00:00
6		15:00	29-Jan-21 15:00
7		20:00	30-Jan-21 11:00
8
19c

Cell Formulas
Range	Formula
C2:C7	C2	=IF(B2<1,B2+N(C1),B2)

 

[hwelshim]

I don't quite get it, can you show what you have so far with XL2BB?

Sure thing:

For Dave's problem.xlsx
	A	B	C
1	Date	Time	In/Out
2	Date	1/22/2021	Out
3	Date	21:34:35	In
4	Date	23:52:46	In
5	Date	04:44:01	Denied
6	Date	23:41:52	Out
7	Date	20:21:36	In
8	Date	1/23/2021
9	Date	14:26:50	In
10	Date	11:31:39	Denied
11	Date	23:27:12	In
12	Date	15:20:40	Out
13	Date	08:19:08	In
14	Date	20:17:02	Out
15	Date	07:03:16	Out
16	Date	07:33:38	Denied
17	Date	00:16:49	Out
Sheet1

Cell Formulas
Range	Formula
C2:C7,C9:C17	C2	=CHOOSE(RANDBETWEEN(1,3),"In","Out","Denied")
A2:A17	A2	=IF(ISERROR(INT(B2)),B2,A1)

 

[aRandomHelper]

Can you try this instead?

=IF(INT(B2)=B2,B2,A1)

 

[hwelshim]

=IF(B3<1,A2+B3,B3)

Hi Dave, thanks for your help. This seems really close, but I'm having a hiccup I can't figure out. The time keeps triggering the False option. Any ideas why? Thanks again!

For Dave's problem.xlsx
	A	B	C
1	Date	Time	In/Out
2	1/22/2021	1/22/2021	Out
3	20:17:02	20:17:02	Denied
4	20:17:03	20:17:03	In
5	20:17:04	20:17:04	Denied
6	20:17:05	20:17:05	Out
7	20:17:06	20:17:06	Out
8	1/23/2021	1/23/2021
9	14:26:50	14:26:50	In
10	11:31:39	11:31:39	Denied
11	23:27:12	23:27:12	Out
12	15:20:40	15:20:40	Denied
13	08:19:08	08:19:08	Out
14	20:17:02	20:17:02	In
15	07:03:16	07:03:16	In
16	07:33:38	07:33:38	Denied
Sheet1

Cell Formulas
Range	Formula
C2:C7,C9:C16	C2	=CHOOSE(RANDBETWEEN(1,3),"In","Out","Denied")
A2	A2	=B2
A3:A16	A3	=IF(B3<1,A2+B3,B3)

 

[hwelshim]

=IF(INT(B2)=B2,B2,A1)

@aRandomHelper This worked! Thank you a ton