Time Difference In Days Between Two Times

[shorie7]

Hello All.

Please help. I am trying to create a worksheet on an iPhone. With MUCH difficulty.

I am a lorry driver and what I need is this...

I enter the last date and time I finish work in the appropriate cells of week "1".

And I add the date and time I start work the following week after my weekend into week "2".

Now here comes the problems...

I need excel to tell me how many HOURS / Mins rest I have had between the last entry of LAST week, and the first entry of THIS week.

I must have 45 Hours rest every week.. But sometimes I finish on different days.

The next problem I have is this...

If I DON'T have 45 Hours rest in any one week, the amount I am SHORT by MUST be added to a standard 45 Hours rest period within three weeks and paid back ad hock.

Example...
I normally work Monday to Friday, say, 18:00-02:00 and with only five days the 45 Hours rest usually isn't an issue.

But I need some overtime... So I work Sunday the following week.

Since I finished (Sat morning after Friday night) at 2:00 am, until I work Sunday at 18:00, I have only had 40 hours rest...

So within the next three weeks I MUST take a weekend break of a minimum of 50 hours... 45 of my required break, PLUS pay back the five hours I owed from my overtime (reduced rest).

Complicated I know. That's why I am at a loss.

I am also unable to format cells as h:mm on my mobile. I'm trying to get to a pc but the nights are just so long as a driver.

Can anyone help at all?

I've given as much information as I can, but I can't even find on here where to upload my spreadsheet.. Any suggestions on that too so you can see what I have so far... I'm really trying! ???

Help me please before I collapse and die from time and minute breaks insanity.....

Kind regards

Mark

 

[Dave Patton]

Either of the following will yield the hours that you require.
You can allocate the amount to subsequent weeks.

Excel 2010
	B	C	D	E
1	Week 1	Week 2	Hours	Shortage
2	27-Oct-18 10:30	29-Oct-18 2:30	40.00	5.00
3	27-Oct-18 10:30	29-Oct-18 2:30	40.00	5.00
4b

Cell Formulas
Range	Formula
D2	=(C2-B2)*24
D3	=(C3-B3)*24
E2	=IF(D2<45,45-D2,0)
E3	=(D3<45)*(45-D3)