Difference between date and time

[Joshua]

I am trying to get the difference between date and time into the hh:mm format.
Example:

Start Date End Date Time taken to complete
7-1-09 8:00 AM 7-2-09 8:30 AM 24:30

How can i get it to calculate the difference between the date and the time

 

[Mike H]

Joshua,

Simply subtract the earlier from the later tiem

=B1-A1

and then format that cell with

[h]:mm

Mike

 

[Bernard Liengme]

=B1-A1
Format the cell with custom format [h]:mm to see 2:30 or 24:30, or with
[hh]:mm to see 02:30 or 24:30
The [ ] are needed to get Excel to display more that 24 hours
best wishes

 

[Jacob Skaria]

Sum and difference same as numerics. except that it should be formatted to

[h]:mm:ss

If this post helps click Yes

 

[Joshua]

Thank you that helped alot

I also want to know if anything has gone over a 24hr time limit
before I had it calculate =IF(C3>24,"NO","YES")
so if C3 was 25:00 it would give me a NO or 23:00 a yes
right now it gives me all YES even if it is over 24
Do I have to change the format of =IF cell or input a new formula

Thanks for your time

=B1-A1
Format the cell with custom format [h]:mm to see 2:30 or 24:30, or with
[hh]:mm to see 02:30 or 24:30
The [ ] are needed to get Excel to display more that 24 hours
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

I am trying to get the difference between date and time into the hh:mm
format.
Example:

Start Date End Date Time taken to complete
7-1-09 8:00 AM 7-2-09 8:30 AM 24:30

How can i get it to calculate the difference between the date and the time

 

[Jacob Skaria]

Try. 24 should not be a numeric but should be in time format//

=IF(E24>TIME(24,0,0),"NO","Yes")

If this post helps click Yes
---------------
Jacob Skaria

Thank you that helped alot

I also want to know if anything has gone over a 24hr time limit
before I had it calculate =IF(C3>24,"NO","YES")
so if C3 was 25:00 it would give me a NO or 23:00 a yes
right now it gives me all YES even if it is over 24
Do I have to change the format of =IF cell or input a new formula

Thanks for your time

=B1-A1
Format the cell with custom format [h]:mm to see 2:30 or 24:30, or with
[hh]:mm to see 02:30 or 24:30
The [ ] are needed to get Excel to display more that 24 hours
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

I am trying to get the difference between date and time into the hh:mm
format.
Example:

Start Date End Date Time taken to complete
7-1-09 8:00 AM 7-2-09 8:30 AM 24:30

How can i get it to calculate the difference between the date and the time

 

[T. Valko]

=IF(E24>TIME(24,0,0),"NO","Yes")

Look in Excel help on the TIME function and see what it says about the HOUR
argument.

Try it like this:

=IF(E24>1,"NO","Yes")

--
Biff
Microsoft Excel MVP

Try. 24 should not be a numeric but should be in time format//

=IF(E24>TIME(24,0,0),"NO","Yes")

If this post helps click Yes
---------------
Jacob Skaria

Thank you that helped alot

I also want to know if anything has gone over a 24hr time limit
before I had it calculate =IF(C3>24,"NO","YES")
so if C3 was 25:00 it would give me a NO or 23:00 a yes
right now it gives me all YES even if it is over 24
Do I have to change the format of =IF cell or input a new formula

Thanks for your time

=B1-A1
Format the cell with custom format [h]:mm to see 2:30 or 24:30, or
with
[hh]:mm to see 02:30 or 24:30
The [ ] are needed to get Excel to display more that 24 hours
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


I am trying to get the difference between date and time into the
hh:mm
format.
Example:

Start Date End Date Time taken to
complete
7-1-09 8:00 AM 7-2-09 8:30 AM 24:30

How can i get it to calculate the difference between the date and the
time

 

[Rich/rerat]

Joshua,
With Row#1 as your Field Headers:
"A" Column: Start Time
"B" Column: Finish Time
"C" Column: Total Hrs formatted as [H]:mm:ss
"D" Column: Yes/No

You can use the following formula in your "Yes/No" column, with Row#1 as
your Field Headers:
=IF($A2="","",IF((B2-A2)*24>24,"NO","YES"))
This will give you a "YES" response for 24 hrs, if you want a "NO" response
for 24 hrs use this:
=IF($A2="","",IF((B2-A2)*24>=24,"NO","YES"))
Drag formula down the column.
You can leave the Cell format for that Column as "General".

--
Add MS to your News Reader: news://msnews.microsoft.com
Rich/rerat
(RRR News) <message rule>
<<Previous Text Snipped to Save Bandwidth When Appropriate>>


Thank you that helped alot

I also want to know if anything has gone over a 24hr time limit
before I had it calculate =IF(C3>24,"NO","YES")
so if C3 was 25:00 it would give me a NO or 23:00 a yes
right now it gives me all YES even if it is over 24
Do I have to change the format of =IF cell or input a new formula

Thanks for your time

=B1-A1
Format the cell with custom format [h]:mm to see 2:30 or 24:30, or with
[hh]:mm to see 02:30 or 24:30
The [ ] are needed to get Excel to display more that 24 hours
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

I am trying to get the difference between date and time into the hh:mm
format.
Example:

Start Date End Date Time taken to
complete
7-1-09 8:00 AM 7-2-09 8:30 AM 24:30

How can i get it to calculate the difference between the date and the
time