Combining Date and Time

[PCRIDE]

Hi, I am building an attendance tracker, log in time compared to scheduled. I have date and time for Scheduled time in two columns, if I combine date and time from two fields using =K6&" "&H6 and returns 45200 0.375. Its not formatting correctly.

Or I could just do something like i.e Late = L, Ontime = O

=IFERROR(IF(AND(HOUR(C6>H6),MINUTE(C6>H6)),"L","O"),"Not Found")

everything is coming up as O when it should be Late. Something is not working here.

 

[PCRIDE]

Hi, I am building an attendance tracker, log in time compared to scheduled. I have date and time for Scheduled time in two columns, if I combine date and time from two fields using =K6&" "&H6 and returns 45200 0.375. Its not formatting correctly.

Or I could just do something like i.e Late = L, Ontime = O

=IFERROR(IF(AND(HOUR(C6>H6),MINUTE(C6>H6)),"L","O"),"Not Found")

everything is coming up as O when it should be Late. Something is not working here.

I think I need more logic come to think of it in the second example.

Figured it out. Thanks!

=IF(AND(HOUR(C40)=HOUR(H40),MINUTE(C40)>MINUTE(H40)),"L",IF(AND(HOUR(C40)>HOUR(H40),MINUTE(C40)>MINUTE(H40)),"L","O"))

 

[AhoyNC]

To combine date and time
=K6+H6

Maybe something like this for L or O.

Book2
	A	B	C	D	E	F	G	H
6			10:30 AM	L				10:15 AM
7
8			Date	Time	combined
9			1/12/2024	10:15 AM	1/12/2024 10:15
Sheet1

Cell Formulas
Range	Formula
D6	D6	=IFERROR(IF(C6>H6,"L","o"),"Not Found")
E9	E9	=C9+D9

 

[PCRIDE]

To combine date and time
=K6+H6

Maybe something like this for L or O.

Book2
	A	B	C	D	E	F	G	H
6			10:30 AM	L				10:15 AM
7
8			Date	Time	combined
9			1/12/2024	10:15 AM	1/12/2024 10:15
Sheet1

Cell Formulas
Range	Formula
D6	D6	=IFERROR(IF(C6>H6,"L","o"),"Not Found")
E9	E9	=C9+D9

Ok just a basic +, I'll try that.

Can you also provide any suggestions for the logout?

If the schedule is until 6:00 PM, but the user logs out at 11:29 AM (before noon), how do I handle the logic I put above, checking minute > or < , since its the time has rolled over past noon.
So my logic won't work in this scenario because minute is not less than the scheduled minute.

 

[PCRIDE]

I just combined the fields, now I can just use greater and less than. Problem solved! Thanks again AhoyNC

 

[AhoyNC]

You're welcome. Thanks for the feedback.