Formula for reducing time if a certain time has passed

[Balmer07]

Hi,

My head has been pickled trying to figure this out, here's the problem (it's also on google sheets):

I am trying to show a live production rate which is based on time and units completed.

Start time in the morning is 8am
I have a cell which shows the live time beside it
Below this I need a cell calculating the difference between 8 am and the current time (this needs to be hours and minutes as a decimal)
-In this formula I need it to calculate that if 10.45am has passed then 15 minutes (tea break) are reduced off the time difference. the same then needs to happen at 2pm but 30 minutes (lunch) need deducted off.
Beside this the units produced are divided by the time calculated in the line above

I hope this makes sense.

Steven

 

[PatOBrien198]

where C3 is current time and C2 is 8:00 AM

=(C3-C2)-(0.25/24)*IF(AND(MINUTE(NOW())>=45,HOUR(NOW())>=10),1,0)-(0.5/24)*IF(HOUR(NOW())>=14,1,0)

 

[Balmer07]

Almost there, but something not quite right just yet

Currently cell 'C2' contains 08:00, Cell 'C3' contains the formula =now() so that a live time is recorded

However I inputted your formula in cell 'D2' and it has only taken off 30 minutes instead of 45.

Any ideas?

thanks,

 

[PatOBrien198]

what time zone are you in?

 

[PatOBrien198]

wait.... I see the problem with my formula...
it should be:

=(C3-C2)-(0.25/24)*IF(OR(HOUR(NOW())>10,AND(MINUTE(NOW())>=45,HOUR(NOW())>=10)),1,0)-(0.5/24)*IF(HOUR(NOW())>=14,1,0)

 

[Balmer07]

Hi,

For some reason when I put your first formula in at the start it didn't work, but I tried it again and it worked. Both formula's work. The end formula I have in the cell can be seen below. I added the 'MOD' feature so I could have the hours as a decimal.

Massive thank you for your help on this one, really appreciate it!!

=MOD((B8-B7)-(0.25/24)*IF(AND(MINUTE(NOW())>=45,HOUR(NOW())>=10),1,0)-(0.5/24)*IF(HOUR(NOW())>=14,1,0),1)*24


thanks
Steven

 

[Balmer07]

Pat,

I have another query for you regarding this actually if you can be of any help?

We want to analyse the production rate from 8am-10.30am, 10.45am-1.30pm, and 2pm-4.30pm

So to do this we want to record the production figure at 10.30, 1.30 and 4.30, like a snap shot of the each time period.

I hope this makes sense?

Please advise,

 

[PatOBrien198]

Actually, the first formula works only if the minute is >= 45. You need the OR condition to test if it's after 11:00.

Regarding Q2, unless you've got someone saving the prod numbers, you could use a macro with an Application.OnTime statement that copies one cells value to another.

 

[Balmer07]

Hi Pat,

You are correct and we didn't realise until the after 10.45, the second formula is correct! thanks!

On Q2, we are using google sheets and I don't feel as comfortable working with macros/script on it. I have contacted google to see if they can help!

thanks for your help!