Calculate a value between 2 times
we pay a nightshift allowance between 18:00 and 6:00.
I have a timesheet with start and end times. Say the shift started at 15:00 and ended at 23:00, I need an answer of 5, or if the shift started at 04:30 and ended at 12:00 I need an answer of 1.5 thanks |
Calculate a value between 2 times
You can use the following formulas. Assume that the start time is in
A1 and the end time is in C1. To calculate the number of hours between start and 6:00:00, use =MAX(0,TIME(6,0,0)-A1)*24 To calculate the number of hours between 18:00:00 and end time, use =MAX(0,C1-TIME(18,0,0)+(C1<TIME(18,0,0)))*24 To calculate the number of hours worked between 6:00:00 and 18:00:00, excluding hours between 18:00 and 6:00:00, use =(MIN(C1,TIME(18,0,0))-MAX(A1,TIME(6,0,0))+(C1<A1))*24 Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Mon, 24 Aug 2009 12:49:13 -0700, Maresa wrote: we pay a nightshift allowance between 18:00 and 6:00. I have a timesheet with start and end times. Say the shift started at 15:00 and ended at 23:00, I need an answer of 5, or if the shift started at 04:30 and ended at 12:00 I need an answer of 1.5 thanks |
All times are GMT +1. The time now is 08:51 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com