Share via

conditional formula for attendance

Anonymous
2017-05-02T12:39:31+00:00

hi friends i have one function query

I want to put function in attendance sheet, i.e if someone is coming late so i want his minutes to be deduct from total hours complete

TIME IN TIME OUT O.T L.T TOTAL WORKED
9:35:00 18:30:00 ?function for late coming - output in minutes in time format 9:55
9:30:00 19:30:00 ?function for overtime - output in minutes in time format 9:00

in above table in first row i want output for LAte coming in minutes in Time format

& on second row Output for ovrtime in minutes in time format.

Thanks

Revert

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2017-05-02T14:35:10+00:00

    Hi Sandeep,

    For Overtime calculation, please use the following formula:

    =[TOTAL WORKED]-TIME(8,0,0)

    Replace [total worked] with the reference to the cell in the row in that column.

    For late calculation, you would need to provide a default start time for the employee but I'll show a version that has a hard-coded value you can change:

    =[TIME IN]-TIME(9,30,0)

    Replace time in the same way you did above but with the time in column. Also you can change the values in the time function to whatever the start time should be, it is currently set to 9:30 is starting time.

    Please let me know if this answers your question by marking as answer and if you need further assistance.

    Thanks,

    EBW

    Was this answer helpful?

    0 comments