Date Calc in hours

[MWH]

I'm trying to perform a date calc to produce a variance in hours
ex cell A1 3/27/2006 2100, cell B1 3/28/2006 0100, cell C1 =b1-a1, results I
would like to see is 4
This spreadsheet will be used for scheduling so both positive and negative
numbers will be used for variance numbers.

 

[Guest]

Mark,
Depending on whether you want to round or truncate to the number of whole
hours, use one of the following in cell C1:

=ROUND((A2-A1)*24,0)
or
=INT((A2-A1)*24)

HTH,
Ryan

 

[Peo Sjoblom]

Use excel time format and just subtract, if this is some sort of import and
there are never more than 24 hours difference you can use

=MOD(TEXT(MID(B1,FIND(" ",B1)+1,255),"00\:00")-TEXT(MID(A1,FIND("
",A1)+1,255),"00\:00"),1)

if more than 24 hours you would need to use the dates as well, more ugly
looking

=(LEFT(B1,FIND(" ",B1)-1)+TEXT(MID(B1,FIND("
",B1)+1,255),"00\:00"))-(LEFT(A1,FIND(" ",A1)-1)+TEXT(MID(A1,FIND("
",A1)+1,255),"00\:00"))

format as time will return 4:00

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon

 

[Peo Sjoblom]

I think you missed that he is not using excel time (hh:mm) he is using 2100
and 0100

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon

 

[MWH]

Still get an error message

Mark,
Depending on whether you want to round or truncate to the number of whole
hours, use one of the following in cell C1:

=ROUND((A2-A1)*24,0)
or
=INT((A2-A1)*24)

HTH,
Ryan

 

[MWH]

Works good except both will not return a value over 24, sometimes variance I
will have will be greater than 24 hrs, also variances may be a negative
number

Use excel time format and just subtract, if this is some sort of import
and there are never more than 24 hours difference you can use

=MOD(TEXT(MID(B1,FIND(" ",B1)+1,255),"00\:00")-TEXT(MID(A1,FIND("
",A1)+1,255),"00\:00"),1)

if more than 24 hours you would need to use the dates as well, more ugly
looking

=(LEFT(B1,FIND(" ",B1)-1)+TEXT(MID(B1,FIND("
",B1)+1,255),"00\:00"))-(LEFT(A1,FIND(" ",A1)-1)+TEXT(MID(A1,FIND("
",A1)+1,255),"00\:00"))

format as time will return 4:00

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon

 

[Peo Sjoblom]

The second will return a value over 24 hours, just use [hh]:mm as format,
for example if you change the date to
3/29/06 0100 the second will return 28:00, it won't work with negative times
unless you change the date format to 1904 or use decimal hours
--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon

Works good except both will not return a value over 24, sometimes variance
I will have will be greater than 24 hrs, also variances may be a negative
number