Check in/Check out Sheet (Hours, Mins, AM/PM Columns)

M

Mack23

New Member
Joined
Aug 11, 2021
Messages
28
Office Version
  1. 365
Platform
  1. Windows
Hello,

I have an issue where I have a start time and an end time with trying to get the total minutes worked during those times. I have a formula that is working besides when there is a 12am for an end time. Column 1 is trying to find the total minutes worked. I can add a date column if need be, but I need some assistance with the midnight issue.

Start HourStart MinsAM/PMEnd HourEnd MinsAM/PM2Column1
2​
4​
AM
3​
4​
PM
780​
10​
0​
PM
12​
0​
AM
840​
10​
0​
PM
1​
0​
AM
180​
0​

Here is the formula I am using in Column 1:
=((H3+IF(AND(J3="PM",H3<12),12,0))*60+I3+IF(((H3+IF(AND(J3="PM",H3<12),12,0))*60+I3)<((E3+IF(AND(G3="PM",E3<12),12,0))*60+F3),24*60,0))-((E3+IF(AND(G3="PM",E3<12),12,0))*60+F3)

Thank you for any help!
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Sort by date Sort by votes
Hi Mack23,

I would recreate the start and end times then if end is < start add 1 day to the end time.

Mack23.xlsx
ABCDEFGHI
1Start HourStart MinsAM/PMEnd HourEnd MinsAM/PM2StartEndColumn1
224AM34PM2:04 AM3:04 PM780
3100PM120AM10:00 PM12:00 AM120
4100PM10AM10:00 PM1:00 AM180
1st
Cell Formulas
RangeFormula
G2:G4G2=TIME(A2*(A2&C2<>"12AM")+(12*(C2="PM")),B2,0)
H2:H4H2=TIME(D2*(D2&F2<>"12AM")+(12*(F2="PM")),E2,0)
I2:I4I2=IF(H2<G2,1+H2-G2,H2-G2)*1440
 
Upvote 0
Toadstool said:
Hi Mack23,

I would recreate the start and end times then if end is < start add 1 day to the end time.

Mack23.xlsx
ABCDEFGHI
1Start HourStart MinsAM/PMEnd HourEnd MinsAM/PM2StartEndColumn1
224AM34PM2:04 AM3:04 PM780
3100PM120AM10:00 PM12:00 AM120
4100PM10AM10:00 PM1:00 AM180
1st
Cell Formulas
RangeFormula
G2:G4G2=TIME(A2*(A2&C2<>"12AM")+(12*(C2="PM")),B2,0)
H2:H4H2=TIME(D2*(D2&F2<>"12AM")+(12*(F2="PM")),E2,0)
I2:I4I2=IF(H2<G2,1+H2-G2,H2-G2)*1440
Click to expand...
Hi Toadstool,

This definitely is on the right track. It fixes the issue with the pm to am, but trying to break it, I found that now it is not calculating from 12 pm correctly. Are we able to add an if statement in the time function to help clear up what the formula is looking for? The last row is showing the issue; I used your formulas from above for the Start Time/End Time/Total Min columns.

Start HourStart MinsAM/PMEnd HourEnd MinsAM/PM2Start TimeEnd TimeTotal Min
2​
4​
AM
3​
4​
PM
2:04 AM​
3:04 PM​
780​
10​
0​
PM
12​
0​
AM
10:00 PM​
12:00 AM​
120​
10​
0​
PM
2​
0​
AM
10:00 PM​
2:00 AM​
240​
12​
0​
PM
2​
0​
PM
12:00 AM​
2:00 PM​
840​

Thank you
 
Upvote 0
Another attempt. I'm finding this tricky without dates or normal time formats.

Mack23.xlsx
ABCDEFGHI
1Start HourStart MinsAM/PMEnd HourEnd MinsAM/PM2StartEndColumn1
224AM34PM2:04 AM3:04 PM780
3100PM120AM10:00 PM12:00 AM120
4100PM20AM10:00 PM2:00 AM240
5120PM20PM12:00 PM2:00 PM120
6120AM100PM12:00 AM10:00 PM1320
7100PM120AM10:00 PM12:00 AM120
2nd
Cell Formulas
RangeFormula
G2:G7G2=TIME(IF(A2&C2="12AM",0,IF(A2&C2="12PM",12,A2+(12*(C2="PM")))),B2,0)
H2:H7H2=TIME(IF(D2&F2="12AM",24,IF(D2&F2="12PM",12,D2+(12*(F2="PM")))),E2,0)
I2:I7I2=IF(H2<G2,1+H2-G2,H2-G2)*1440
 
Upvote 0
Solution
Toadstool said:
Another attempt. I'm finding this tricky without dates or normal time formats.

Mack23.xlsx
ABCDEFGHI
1Start HourStart MinsAM/PMEnd HourEnd MinsAM/PM2StartEndColumn1
224AM34PM2:04 AM3:04 PM780
3100PM120AM10:00 PM12:00 AM120
4100PM20AM10:00 PM2:00 AM240
5120PM20PM12:00 PM2:00 PM120
6120AM100PM12:00 AM10:00 PM1320
7100PM120AM10:00 PM12:00 AM120
2nd
Cell Formulas
RangeFormula
G2:G7G2=TIME(IF(A2&C2="12AM",0,IF(A2&C2="12PM",12,A2+(12*(C2="PM")))),B2,0)
H2:H7H2=TIME(IF(D2&F2="12AM",24,IF(D2&F2="12PM",12,D2+(12*(F2="PM")))),E2,0)
I2:I7I2=IF(H2<G2,1+H2-G2,H2-G2)*1440
Click to expand...
I can put a date column in. I was actually going to put one in at the beginning anyway to help with my pivots and charts that I was going to create. If a date helps, then definitely use that if it would be easier for the formulas.
 
Upvote 0
If you have the dates and normal time formats then it is simply maths.

Mack23.xlsx
ABCDE
1Start DateStart TimeEnd DateEnd TimeColumn1
201-Jan-222:0401-Jan-2215:04780
301-Jan-2222:0002-Jan-220:00120
401-Jan-2222:0002-Jan-222:00240
502-Jan-2212:0002-Jan-2214:00120
602-Jan-220:0002-Jan-2222:001320
702-Jan-2222:0003-Jan-226:00480
3rd
Cell Formulas
RangeFormula
E2:E7E2=((C2+D2)-(A2+B2))*1440
 
Upvote 0
You must log in or register to reply here.

Similar threads

HappyChappy
  • Solved
tidying up formulas
Replies
4
Views
142
HappyChappy
HappyChappy
HappyChappy
  • Solved
Converting time problem
Replies
2
Views
113
HappyChappy
HappyChappy
S
  • Question
Formula for Equity Vesting
Replies
6
Views
332
Dave Patton
D
HappyChappy
  • Question
Bracket Error
Replies
3
Views
128
Peter_SSs
Peter_SSs
K
  • Solved
Average Based on Criteria
Replies
3
Views
148
Fluff
Fluff

Forum statistics

Threads
1,214,936
Messages
6,122,340
Members
449,079
Latest member
rocketslinger

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top