date time forumla

[james rockford]

Hello to all, I need to find a formula that will take two dates and two different time ( time in and time out) and subtract them to give me the time what was the balance used

I.E
Date time in date completed time out time used
5/1/2018 10 AM 5/2/2018 11: PM ????


thanks for the help and have a great day

 

[cerfani]

type the values like this...

5/1/2018 10:00
5/2/2018 23:00

i think you can use 12 hour and just put am or pm at the end (use a space before)... just make sure excel autoformats them after you type them in and they will be seen as numbers

excel will recognize they are dates (which are decimal values underlying) and you can do any arithmetic you want

 

[james rockford]

Hi Cerfani....thanks for your comeback but I need to have two different cells on date and one time ........

 

[cerfani]

then you can concatenate the text and put it inside the value function which converts text (if posssible like with date text) to numbers.

=VALUE(A1&" "&B1)-VALUE(C1&" "&D1)

A1 is the date (ex. "1/1/2018")
B1 is time (ex. "5:00 PM")
C1 is the date (ex. "1/1/2018")
D1 is time (ex. "2:00 PM")

in this example you would get the decimal value 3/24 or 0.125

 

[jtakw]

Hi,

If your Date and Time are Real Date and Time values (numeric):

Book1
	A	B	C	D	E	F
1	Date	Time In	Date Completed	Time Out		Time Used
2	5/1/2018	10:00 AM	5/2/2018	11:00 PM		37:00
3
4						F2 formatted as [h]:mm
Sheet55

Cell Formulas
Range	Formula
F2	=SUM(C2,D2)-SUM(A2,B2)

 

[Dave Patton]

Format as number

Excel 2010
	A	B	C	D	E
1	1-Jan-18	11:00 AM	1-Jan-18	2:00 PM	3.00
2	1-Jan-18	11:00 AM	1-Jan-18	11:00 PM	12.00
3
3a

Cell Formulas
Range	Formula
E1	=((C1+D1)-(A1+B1))*24
E2	=((C2+D2)-(A2+B2))*24