[Solved] Date Function

[EMOHAMMED]

Hi experts -- Need you help with this date functions

The file attached is basically a payroll file with data

Clock in date Clock in time Clock End Date Clock End time Overtime Hrs Basic Hrs
C D E F M N

say row 10 - basically the logic is this
a) if clock in time is less than 6:00am then Overtime = is 6:00am - clockin time (hours in 2 dp)
b) if clock out time is greater than 2:30 pm then overtime = clock out time - 2:00 pm (2 d.p)

c) I am not getting this formula to work correctly ---
is the format for time is 10:00AM or 10:00 AM (space)
=IF(D10<$L$2,TRUE,FALSE)

Please help

 

[Yousuf Shovon]

c) I am not getting this formula to work correctly ---
is the format for time is 10:00AM or 10:00 AM (space)
=IF(D10<$L$2,TRUE,FALSE)

Hello EMOHAMMED,
Welcome to ExcelDemy forum! Thanks for sharing your experience with us.

Regarding your problem, there is no functionality issue with your formula. However, you are using an incorrect time format with 10:00AM (no space). As you can see, Excel does not recognize this as a time format, and by default it shifts to the left side of the cell as text. However, the time shifts right of the cell to 10:00 AM (with space).
You can change the format manually while entering the time value. See this article:

How to Change Time Format in Excel​

However, if you need to use both the formats, you can use this formula instead:

For (Before 6:00 am) column in K10, use the formula and drag it down:

=IF(VALUE(TEXT(SUBSTITUTE(SUBSTITUTE(TRIM(D10),"AM"," AM"),"PM"," PM"),"h:mm:ss AM/PM")) < VALUE(TEXT(SUBSTITUTE(SUBSTITUTE(TRIM($L$2),"AM"," AM"),"PM"," PM"),"h:mm:ss AM/PM")), TRUE, FALSE)

For (After 2:30 pm) column, use this formula in L10:

=IF(VALUE(TEXT(SUBSTITUTE(SUBSTITUTE(TRIM(F10),"AM"," AM"),"PM"," PM"),"h:mm:ss AM/PM")) < VALUE(TEXT(SUBSTITUTE(SUBSTITUTE(TRIM($L$3),"AM"," AM"),"PM"," PM"),"h:mm:ss AM/PM")), TRUE, FALSE)

These formulas seem a bit complex but they do the task perfectly.
I am attaching the result file here. Let us know how it goes.
Regards,
Yousuf Shovon