Date Calc in hours
 

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.


--
Mark W. Hanford

Date Calc in hours
 

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" wrote:

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.


--
Mark W. Hanford

Date Calc in hours
 

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




"MWH" wrote in message
...
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.


--
Mark W. Hanford

Date Calc in hours
 

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




"Ryan Poth" wrote in 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" wrote:

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.


--
Mark W. Hanford

Date Calc in hours
 

Still get an error message
"Ryan Poth" wrote in 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" wrote:

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.


--
Mark W. Hanford

Date Calc in hours
 

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




"Peo Sjoblom" wrote in message
...
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




"MWH" wrote in message
...
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.


--
Mark W. Hanford

Date Calc in hours
 

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




"MWH" wrote in message
...
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




"Peo Sjoblom" wrote in message
...
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




"MWH" wrote in message
...
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.


--
Mark W. Hanford