Share via

Excel of Sheets Formula to Calculate Minutes Late from a Specific Time

Anonymous
2020-12-06T21:56:54+00:00

Hi,

I use a google form for student attendance but can use a Microsoft Forms as well, hence I'm posting the question here. Students submit a survey with their name and class on it. When they hit submit, I use the timestamp from the survey to determine who was present and on time. Class starts at 10:30am and I need a formula to highlight latecomers with a color (yellow, let's say). More importantly, I need a formula to calculate how many minutes a student was late by.

So, in the attached sample Google Sheet, I would love to have the final column show me how many minutes past the 10:30 start time did a student sign in. Ideally, the final column "minutes late" will show number of minutes a student in that row was late. For example, if a student signed in at 10:55am. I would love to see the last column show "00:25:00" indicating they signed in 25 minutes past the class start time.

Sample attendance workbook

Is this possible to achieve in Excel, Microsoft Forms, or Google Forms through a formula? 

Thanks for your help,

Jay

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

Answer accepted by question author

Anonymous
2020-12-09T15:16:50+00:00

Hi, 

Are you familiar with how date and time is stored in Excel?

date is basically the whole number, while the time of the day is in decimal points

For example, your cell A2 actually contains the number 44150.37500000 formatted as date and time, and your cell B2 actually contains the number 0.43750000 formatted as time.

You can use that fact to perform both of your tasks, calculating your late time as (A2-ROUNDDOWN(A2;0))-B2, ie (date and time minus date) minus time. If your that number is positive that are your hours, minutes and seconds late.

Br,

Was this answer helpful?

20+ people found this answer helpful.
0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2020-12-07T07:50:17+00:00

    Hi Jay,

    In your Excel worksheet, put the following formula in cell F2.

    =TEXT((TEXT(A2,"h:mm:ss")-B2),"h:mm:ss")

    #Value! cells displayed in column F indicates that the student has arrived early. If you don't want #Value! to display, you can use this formula: =IFERROR(TEXT((TEXT(A2,"h:mm:ss")-B2),"h:mm:ss"),"")

    Best regards,

    Madoc

    Was this answer helpful?

    7 people found this answer helpful.
    0 comments
  2. Anonymous
    2020-12-09T07:27:14+00:00

    Hi Jay,

    Have you checked the above reply? Feel free to post back if you need further assistance.

    Best regards,

    Madoc

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments