Issue Subtracting Time

Liz_I3

Well-known Member
Joined
Dec 30, 2002
Messages
647
Office Version
  1. 2016
Platform
  1. Windows
Hello
I want to get hours worked. I am using the below to formulas. The first one is just a straight Start Time - End Time but if that is over 30 minutes I want to use the second formula to subtract 30 minutes for Lunch. They both work but how do I put this into an iif statement. I cannot get the iif <.50 correct


=Format([MaxOfEndTime]-[MinOfStartTime]),"Short Time")


=Format([MinOfStartTime]-1-DateAdd("n",-30,[MaxOfEndTime]),"Short Time")

Thanks
L
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
I got this to work, but if anyone has a better method I would appreciate any ideas

=IIf(DateAdd("n",30,[MinOfStartTime])>[MaxOfEndTime],Format(([MaxOfEndTime]-[MinOfStartTime]),"Short Time"),Format([MinOfStartTime]-1-DateAdd("n",-30,[MaxOfEndTime]),"Short Time"))

Thanks
L
 
Upvote 0
In order to explore this, I think we'd have to know the data type for MinOfStartTime, etc. It could be DateTime or long, or integer. Or maybe something else.
It's kind of a curiosity, because you're using the built in format of Short Time in an expression, while I think you could set that in the table field - unless that field isn't Date/Time.
 
Upvote 0

Forum statistics

Threads
1,223,230
Messages
6,170,883
Members
452,364
Latest member
springate

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