Time formula

R

rkramapo

New Member
Joined
Feb 28, 2024
Messages
8
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi- I have a question on how I can edit this IF formula to reflect the correct time when an employee schedule end time is at 12am. The total works for times after 12am. Let's say cell p40 is 6p and cell o40 is 12am and any hours worked greater than 5 requires a half hour break- the total hours pops up as -18

=IF(P40*24-O40*24<5, P40*24-O40*24, P40*24-O40*24-0.5)
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Sort by date Sort by votes
In the example you laid out, if I recreate it as you have you have described, it get 17.5, not -18. What we don't know is if you have a date component to go along with your time piece.

In cases like this, it is often very helpful if you post a small sample of your data showing different circumstances/situations, along with your expected results, as so much is dependent upon your actual data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
 
Upvote 0
Dates and Time 2024.xlsm
OPQ
39StartEnd
4018:00:0012:0017.50
2e
Cell Formulas
RangeFormula
Q40Q40=LET(t,(P40-O40+(O40>P40))*24,IF(t<5,t,t-0.5))
 
Upvote 0
Book1
ABCDE
1Employee'sFriday
2 NameShiftHours
3MiledySuper7:30 AM4:00 PM8.00
4Steve Super0.00
5JordanSuper4:00 PM12:15 AM7.75
6 Janet GCash 17:00 AM3:30 PM8.00
7DannaCash 20.00
8Charlotte Cash 33:00 PM11:30 PM8.00
9Luis C FSW 17:30 AM3:30 PM7.50
10John B. FSW 29:00 AM4:00 PM6.50
11JulissaFSW 30.00
12LORETA FSW 44:00 PM11:45 PM7.25
13AliciaFSW40.00
14MarthaFSW612:00 PM8:30 PM8.00
15LucyFSW 144:00 PM11:45 PM7.25
16CARLOS Grill 16:30 AM3:00 PM8.00
17Lionel D. Grill 211:45 AM8:15 PM8.00
18Patty CGrill 30.00
19Ysabel R Grill 44:00 PM12:15 AM7.75
20Clara R. Grill 54:00 PM11:45 PM7.25
21WilliamGrill 60.00
22RyanGrill 70.00
23Patrick Grill 84:00 PM11:45 PM7.25
24TINA DCOOK 1 7:30 AM4:00 PM8.00
25OPEN Cook 211:00 AM6:00 PM6.50
26BRIAN C. Cook 36:30 AM3:00 PM8.00
27GAVIN Cook 49:00 AM5:15 PM7.75
28MaryCook 59:15 AM5:45 PM8.00
29RadcliffCOOK 612:00 PM8:30 PM8.00
30BRIAN O. UT 17:30 AM2:30 PM6.50
31Felipe UT 29:00 AM5:30 PM8.00
32Jean T. UT 34:00 PM12:15 AM7.75
33MELVIN UT 44:00 PM12:15 AM7.75
34EMILIO UT 50.00
35ElimaUT 64:00 PM12:15 AM7.75
36CesarUT 70.00
37OPEN UT 80.00
38Student6:00 PM12:00 AM-18.00
Sheet1
Cell Formulas
RangeFormula
E3:E38E3=IF(D3*24-C3*24<5, D3*24-C3*24, D3*24-C3*24-0.5)
 
Upvote 0
Sorry- first time poster- so the highlighted shift under student should be 5.5 not -18. How can I fix that formula so it can recognize that.
 
Upvote 0
rkramapo said:
Sorry- first time poster- so the highlighted shift under student should be 5.5 not -18. How can I fix that formula so it can recognize that.
Click to expand...
Use Dave Patton's formula from post 3.
Adjusted for your new image which has data in columns C and D instead of O and P, put this formula in cell E3 and copy down to cell E38:
Excel Formula: 
=LET(t,(D3-C3+(C3>D3))*24,IF(t<5,t,t-0.5))
 
Upvote 0
Joe4 said:
Use Dave Patton's formula from post 3.
Adjusted for your new image which has data in columns C and D instead of O and P, put this formula in cell E3 and copy down to cell E38:
Excel Formula: 
=LET(t,(D3-C3+(C3>D3))*24,IF(t<5,t,t-0.5))
Click to expand...
I used his formula, but it is giving me an error with the formula- invalid name formula- is it because I'm using excel 2016?
 
Upvote 0
rkramapo said:
I used his formula, but it is giving me an error with the formula- invalid name formula- is it because I'm using excel 2016?
Click to expand...
Yes, LET is a new function.

But if you are using Excel 2016, why did you list 365 in your profile???
1709146611297.png
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Question Question
Employees per hour calculations
Replies
2
Views
361
Wgoldbaum
W
S
  • Solved
#VALUE
Replies
5
Views
203
myall_blues
myall_blues
SFCChase
  • Question Question
Use Dropdown to search other tabs
Replies
4
Views
138
SFCChase
SFCChase
T
  • Question Question
enter time without colon
Replies
5
Views
119
etaf
etaf
C
  • Solved
Time sheet formulas
Replies
3
Views
327
Joe4
Joe4

Forum statistics

Threads
1,223,886
Messages
6,175,195
Members
452,616
Latest member
intern444

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