auto add 7.5 hrs a2

[Trevor3007]

Hi,

To simplyfy /speed up the procees for users. In a2 I place a start time ( using 24 hr clock) 1500, but I want b2 to add 7.5 to a2 to give the end time of 22:30. I also would like b2 to show a blank field when a2 is blank..

thanks for your help & good luck.

 

[Fluff]

How about

=IF(A2="","",(A2+750)/2400)

 

[Trevor3007]

Hi Fluff,

thanks for geting back to me. I think there is a case of clash of the formulas? As B2 &D2 should be as highlighyed below

mileage & Time Calc.xlsx
	A	B	C	D
1	Time Start	Time End	Length Of Lunch Taken	Total Day Minus Lunch Taken
2	17:30	07:30	01:00	13:00
Time Calc

Cell Formulas
Range	Formula
B2	B2	=IF(A2="","",(A2+750)/2400)
D2	D2	=MOD(B2-A2,1)-C2

15:00	07:30	01:00	15:30
	22:30		7.5

hoping you can solve ?

 

[Fluff]

It doesn't work because you said you would enter a time as 1500 which is not the same as 15:00

 

[Trevor3007]

How about

=IF(A2="","",(A2+750)/2400)

So sorry Fluff,
that should read:-

Time Start	Time End	Length Of Lunch Taken	Total Day Minus Lunch Taken
15:00	07:30	01:00	15:30
	23:30		7.5

A2 = start time B2 =end time( a2 + 7.5), c2 = length of lunch taken, d2 = total day - c2 = 7.5.

so sorry fro the confushion & my busy fingers too

 

[Fluff]

If you are entering an actual time, then use

=IF(A2="","",A2+7.5/24)

 

[Trevor3007]

How about

=IF(A2="","",(A2+750)/2400)

So sorry Fluff,
that should read:-

Time Start	Time End	Length Of Lunch Taken	Total Day Minus Lunch Taken
15:00	07:30	01:00	15:30
	23:30		7.5

A2 = start time B2 =end time( a2 + 8.5), c2 = length of lunch taken, d2 = total day - c2 = 7.5.

so sorry for the confushion & my busy fingers too as I should have stated in my orginal for this post, that my woking day is 8hrs 30 mins , as I am forced to take 1hr lunch, even when I dont take lunch ( which is most days) .

Many thanks fluff for your time and patience.

 

[Trevor3007]

If you are entering an actual time, then use

=IF(A2="","",A2+7.5/24)

wahooooooooooooo. thank you.

 

[Fluff]

You're welcome & thanks for the feedback.

In future when marking as solution, please mark the post with the solution, rather than your post saying it works. I have changed it for you this time.

 

[Trevor3007]