Date and time converting and summation problem

Hi all,

i have attached the relevant excel sheet here,
here is the my problem

when i give Line in Date and Time, those should be added to working days and give the relevant line out Date and time.
in the working days column it consider 9 hours as a working day.
that means 1 working days = 9 hours.
but for the line in & line out date and time it consider normal days and time. 1day=24 hours

as an example answers should be as follows
for 7th row

line out time = 7.15am+9.00hours = 16:15pm
line out date= 14-Mar

i tried with functions and confused because of different time format.
is there anyway to to this using functions? if not shout i use macro?

please help me to solve this problem

thanks in advance

Originally Posted by benishiryo

hi there again rsami. not sure if this is what you need. in H7:
=IF(F7="","",INT(F7+G7+9/24))

in I7:
=IF(F7="","",MOD(G7+9/24,1))

would be good if you can type out the desired results in the sample file so that it is clearer. =)

Hi Benishiryo,
you helped me 2 or 3 days ago and again helped me.
thanks very very much for your help.
sorry my fault,it's bit confusing problem .
i have typed out desired output there now(with this attachment).

according to the time date should be changed.here date depend on time.
as an example if it's 15-Mar and line in time is 20:30(8.30PM) then 35.47 hours(3.94*9) should be added to time.then line out will be 8:17am of 17-Mar

hope you will understand it now.if not let me know and I'll describe in details

thanks again

try this formula

=F7+IF(I7<G7,1,0)

Originally Posted by benishiryo

you're welcome. hmmmm i'm not sure if you got your times right. 35.47 hours is not 35 hours 47 mins. it's 35 hours & 0.47 of an hour. 0.47 of an hour is 28 minutes. if i'm right, then just multiply your additional cell:
=IF(F7="","",INT(F7+G7+C7*9/24))
=IF(F7="","",MOD(G7+C7*9/24,1))

nice,you are correct.i was confused with those hours and minutes. but with your answer i understood every thing .
you are doing a great job and keep up the good works

thanks again

PS: will leave rep later(it doesn't allow me to do so now)

Originally Posted by nflsales

try this formula

=F7+IF(I7<G7,1,0)

thanks very much
rep+