Time Sheet question

T

tfowlerusa

New Member
Joined
Mar 28, 2017
Messages
13
Good morning. I am trying to put a time sheet together and I am having issues with a few formulas. I have a Code Shift column that I am trying to use to sort time worked into a specific code. Code1 hours are 6am to 6pm, Code 2 6pm to 12am, and Code 3 from 12am to 6am. Then placing the time worked into the specific Code Shift Column. I have so problems with adjusting the times in the formula to put the times into the correct column. Also would love to have cells blank when there is not data to calculate. Any help would be appreciated. Thank you Todd

On Call Log.xlsx
ABCDEFGH
2 HoursCode Code 1 ShiftCode 2 ShiftCode 3 Shift
3EmployeeDateStart TimeStop TimeShiftTotal TimeTotal TimeTime
4Varon20-Jan12:00 PM12:18 PMCode 10:18  
58:30 AM9:30 AMCode 11:00  
612:20 PM1:20 PMCode 11:00  
71:45 PM2:00 PMCode 10:15  
83:00 PM3:45 PMCode 10:45  
93:45 PM4:00 PMCode 10:15  
104:00 PM4:15 PMCode 10:15  
115:00 PM6:00 PMCode 3  
126:45 PM7:15 PMCode 3  
1311:30 PM12:00 AMCode 1#VALUE!  
14Code 3  0:00
15Code 3  0:00
16Code 3  0:00
17Code 3  0:00
18Code 3  0:00
19Code 3  0:00
20Code 3  0:00
21Code 3  0:00
22Code 3  0:00
23Code 3  0:00
24Code 3  0:00
25Code 3  0:00
26Code 3  0:00
27Total Hours
Sheet1
Cell Formulas
RangeFormula
E4:E26E4=IF(AND(C4>=TIME(6,0,0), D4<TIME(18,0,0)), "Code 1", IF(AND(C4>=TIME(18,0,0), D4<TIME(24,0,0)), "Code 2", IF(OR(C4<TIME(6,0,0), D4>=TIME(24,0,0)), "Code 3", "Invalid Time")))
F4:F26F4=IF(E4="Code 1", TEXT(D4-C4, "h:mm"), "")
G4:G26G4=IF(E4="Code 2", TEXT(D4-C4, "h:mm"), "")
H4:H5,H7:H10,H13:H26H4=IF(E4="Code 3", TEXT(D4-C4, "h:mm"), "")
H6H6=IF(E6="Code 3", IF(ISNUMBER(C6), IF(ISNUMBER(D6), IF(C6 < D6, TEXT(D6 - C6, "h:mm"), TEXT(1 - C6 + D6, "h:mm")), ""), ""), "")
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
You can set 0 to not show for your sheet several ways; one way is File Options Display
You can try working with numbers not text.
Edit the following to address your requirements.

Book1
ABCDEFGH
1
2 HoursCode 1 ShiftCode 2 ShiftCode 3 Shift
3EmployeeDateStart TimeStop TimeTotal TimeTotal TimeTime
412:00:0012:18:000:180:00
508:30:0009:30:001:000:00
612:20:0013:20:001:000:00
713:45:0014:00:000:150:00
815:00:0015:45:000:450:00
915:45:0016:00:000:150:00
1016:00:0016:15:000:150:00
1117:00:0018:00:001:000:00
1218:45:0019:15:000:000:30
1323:30:0023:59:590:000:29
1418:00:0006:00:0006:006:00
1518:00:0006:00:0006:006:00
1618:00:0006:00:0006:006:00
1718:00:0006:00:0006:006:00
1818:00:0006:00:0006:006:00
194:4830:59:5930:00:00
20
Sheet1
Cell Formulas
RangeFormula
F4:F18F4=IF(AND(C4>=$K$1,C4<$L$1),MIN(D4,$L$1)-C4,0)
G4:G13G4=IF(AND(C4>$L$1,C4<=$M$1),MIN(D4,$M$1)-C4,0)
G14:G18G14=MIN(6/24,IF(C14>=$L$1,D14-C14+(C14>D14),0))
H14:H18H14=IF(C14>=$L$1,D14-C14+(C14>D14),0)-G14
F19:H19F19=SUM(F4:F18)
 
Last edited:
Upvote 0
Thank you. Question what information is in cell K1 and L1? One other question, can not the formula be one for each column since the times will change each day, meaning the Code times will not always be in the same row, might be more or less each day.
 
Last edited:
Upvote 0
Time 2023.xlsm
ABCDEFHKL
1Time Sheet6:00:00 AM6:00:00 PM
2 HoursCode 1 Shift
3DateStart TimeStop TimeTotal Time
412:00:0012:18:000:18
508:30:0009:30:001:00
612:20:0013:20:001:00
713:45:0014:00:000:15
815:00:0015:45:000:45
915:45:0016:00:000:15
1016:00:0016:15:000:15
1117:00:0018:00:001:00
1218:45:0019:15:000:00
1323:30:0023:59:590:00
1408:00:0016:00:008:00
1509:00:0015:00:006:00
1610:00:0018:00:008:00
1711:00:0020:00:007:00
1812:00:0018:00:006:00
1939:48
2e
Cell Formulas
RangeFormula
F4:F18F4=IF(AND(C4>=$K$1,C4<$L$1),MIN(D4,$L$1)-C4,0)
F19F19=SUM(F4:F18)
 
Upvote 0
I included an alternative for first criteria.
You can build formulas for the other 2 criteria; please post an example if you require additional help.

Time 2023.xlsm
ABCDEFHKL
1Time Sheet6:00:00 AM6:00:00 PM
2 HoursCode 1 Shift
3DateStart TimeStop TimeTotal Time
412:00:0012:18:000:180:18
508:30:0009:30:001:001:00
612:20:0013:20:001:001:00
713:45:0014:00:000:150:15
815:00:0015:45:000:450:45
915:45:0016:00:000:150:15
1016:00:0016:15:000:150:15
1117:00:0018:00:001:001:00
1218:45:0019:15:000:000:00
1323:30:0023:59:590:000:00
1408:00:0016:00:008:008:00
1509:00:0015:00:006:006:00
1610:00:0018:00:008:008:00
1711:00:0020:00:007:007:00
1812:00:0018:00:006:006:00
1939:4839:48
2e
Cell Formulas
RangeFormula
F4:F18F4=IF(AND(C4>=$K$1,C4<$L$1),MIN(D4,$L$1)-C4,0)
F19,H19F19=SUM(F4:F18)
H4:H18H4=MAX(0,MIN(D4,$L$1)-MAX(C4,$K$1,0))


or =MAX(0,MIN(D4,0.75)-MAX(C4,0.25,0))
 
Upvote 0
I updated a few things since I saw you actually added times in K and L. I decides to add times for all three codes I need and I am having an issue with Code 2. lines 12 and 13 should go into Code 2 cells but is not. It looks like Code 1 and 3 are working as they should. Am I missing something?

On Call Log.xlsx
ABCDEFGHIJKLMNO
16:0018:0018:010:000:016:00
2 HoursCode 1 ShiftCode 2 ShiftCode 3 ShiftCode 1Code 2Code 3
3EmployeeDateStart TimeStop TimeTotal TimeTotal TimeTime
4Varon20-Jan12:0012:180:18  
58:309:301:00  
612:2013:201:00  
713:4514:000:15  
815:0015:450:45  
915:4516:000:15  
1016:0016:150:15  
1117:0018:001:00  
1218:4519:15   
1323:300:00   
1421-Jan1:002:00  1:00 
155:456:00  0:15
166:159:002:45  
17   
18   `
19   
20   
21   
22   
23   
24   
25   
26   
27Total Hours Worked7:330:001:15
28
Sheet1
Cell Formulas
RangeFormula
E4:E26E4=IF(AND(C4>=$J$1, C4<$K$1), MIN(D4, $K$1)-C4, "")
F4:F26F4=IF(AND(C4>=$L$1, C4<$M$1), MIN(D4, $M$1)-C4, "")
G4:G26G4=IF(AND(C4>=$N$1, C4<$O$1), MIN(D4, $O$1)-C4, "")
E27:G27E27=SUM(E4:E26)
K14K14=IF(AND(124>=$L$1, C12<$M$1), MIN(D12, $M$1)-MAX(C12, $L$1), "")
 
Upvote 0
Try reviewing the formula with Formula Evaluate.
You are using 0 time for midnight; consequently, the formula evaluates to 0.

Try reviewing the following and/or search for Time Sheets on the forum.
I do not work with time sheets so I just quickly created a sheet.
Please check with your data.

Time 2023.xlsm
ABCDEFHK
1Time Sheet
2 Hours6 am to 6 pm6 pm to MidnightMidnight to 6 am
3DateStart TimeStop TimeTimeTimeTime
412:00:0012:18:000:18  
508:30:0009:30:001:00  
612:20:0013:20:001:00  
713:45:0014:00:000:15  
815:00:0015:45:000:45  
915:45:0016:00:000:15  
1016:00:0023:59:592:006:00 
1120:00:0023:55:00 3:55 
1218:45:0019:15:00 0:30 
1323:30:0023:59:59 0:30 
1400:00:0106:00:00  6:00
1501:00:0013:00:007:00 5:00
1600:15:0018:00:0012:00 5:45
1700:15:0020:00:0012:002:005:45
1800:00:0123:59:5912:006:006:00
1948:3318:5528:30
20
2e
Cell Formulas
RangeFormula
F4:F18F4=MAX(0,MIN(D4,0.75)-MAX(C4,0.25,0))
F19,K19,H19F19=SUM(F4:F18)
H4:H18H4=CEILING(MAX(0,MIN(D4,1)-MAX(C4,0.75,0)),1/(24*60))
K4:K18K4=CEILING(MAX(0,MIN(0.25,D4)-MAX(1/(24*60*60),C4,0)),1/(24*60))
 
Upvote 0
I edited several cells including the criteria.
Another example that you can text and edit.

Time 2023.xlsm
CDEFGJKLMNO
106:00:0018:00:0018:00:0100:00:0000:00:0106:00:00
2 HoursCode 1 ShiftCode 2 ShiftCode 3 ShiftCode 1Code 2Code 3
3Start TimeStop TimeTotal TimeTotal TimeTime
412:00:0012:18:000:18  
508:30:0009:30:001:00  
612:20:0013:20:001:00  
713:45:0014:00:000:15  
815:00:0015:45:000:45  
915:45:0016:00:000:15  
1016:00:0016:15:000:15  
1117:00:0018:00:001:00  
1218:45:0019:15:00 0:30 
1323:30:0000:00:00 0:30 
1401:00:0002:00:00  1:000.0208333
1505:45:0006:00:00  0:15
1606:15:0009:00:002:45  
2ee
Cell Formulas
RangeFormula
E4:E16E4=IF(AND(C4>=$J$1, C4<$K$1), MIN(D4, $K$1)-C4, "")
F4:F16F4=IF(AND(C4>$K$1, D4<=1), MIN(D4, $M$1)-C4, 0)
G4:G16G4=IF(AND(C4>=$N$1, C4<=$O$1), MIN(D4, $O$1)-C4, "")
K14K14=IF(AND(124>=$L$1, C12<$M$1), MIN(D12, $M$1)-MAX(C12, $L$1), "")
 
Last edited:
Upvote 0
Time 2023.xlsm
CDEFHK
1Time Sheet
2 Hours6 am to 6 pm6 pm to MidnightMidnight to 6 am
3Start TimeStop TimeTimeTimeTime
412:00:0012:18:000:18  
508:30:0009:30:001:00  
612:20:0013:20:001:00  
713:45:0014:00:000:15  
815:00:0015:45:000:45  
915:45:0016:00:000:15  
1016:00:0000:00:002:006:00 
1120:00:0000:00:00 4:00 
1218:45:0019:15:00 0:30 
1323:30:0000:00:00 0:30 
1406:00:00  6:00
1513:00:007:00 6:00
1600:15:0018:00:0012:00 5:45
1700:15:0020:00:0012:002:005:45
1800:00:0100:00:0012:006:006:00
1948:3319:0029:30
2eee
Cell Formulas
RangeFormula
F4:F18F4=MAX(0,MIN(D4,0.75)-MAX(C4,0.25,0))
F19,K19,H19F19=SUM(F4:F18)
H4:H18H4=MAX(0,MIN(D4,1)-MAX(C4,0.75,0))
K4:K18K4=CEILING(MAX(0,MIN(0.25,D4)-MAX(1/(24*60*60),C4,0)),1/(24*60))
 
Upvote 0
You must log in or register to reply here.

Similar threads

JenniferMurphy
  • Solved
Is there a way to format date-time with proper alignment?
Replies
8
Views
468
JenniferMurphy
JenniferMurphy
C
  • Solved
Convert and copy to Date
Replies
5
Views
563
Micron
M
P
  • Question Question
Identify previous steps in table
Replies
0
Views
360
Pestomania
P
E
  • Question Question
What's the best way to pull data from this sheet?
Replies
1
Views
286
bobaol
B
C
  • Solved
Averaging + & - times in Text format
2
Replies
10
Views
586
Alex Blakenburg
A

Forum statistics

Threads
1,223,882
Messages
6,175,164
Members
452,615
Latest member
bogeys2birdies

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