If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Difference between date and time
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 |
#2
|
|||
|
|||
Difference between date and time
Joshua,
Simply subtract the earlier from the later tiem =B1-A1 and then format that cell with [h]:mm Mike "Joshua" wrote: 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 |
#3
|
|||
|
|||
Difference between date and 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 "Joshua" wrote in message ... 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 |
#4
|
|||
|
|||
Difference between date and time
Sum and difference same as numerics. except that it should be formatted to
[h]:mm:ss If this post helps click Yes --------------- Jacob Skaria "Joshua" wrote: 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 |
#5
|
|||
|
|||
Difference between date and time
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(C324,"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 "Bernard Liengme" wrote: =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 "Joshua" wrote in message ... 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 |
#6
|
|||
|
|||
Difference between date and time
Try. 24 should not be a numeric but should be in time format//
=IF(E24TIME(24,0,0),"NO","Yes") If this post helps click Yes --------------- Jacob Skaria "Joshua" wrote: 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(C324,"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 "Bernard Liengme" wrote: =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 "Joshua" wrote in message ... 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 |
#7
|
|||
|
|||
Difference between date and time
=IF(E24TIME(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(E241,"NO","Yes") -- Biff Microsoft Excel MVP "Jacob Skaria" wrote in message ... Try. 24 should not be a numeric but should be in time format// =IF(E24TIME(24,0,0),"NO","Yes") If this post helps click Yes --------------- Jacob Skaria "Joshua" wrote: 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(C324,"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 "Bernard Liengme" wrote: =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 "Joshua" wrote in message ... 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 |
#8
|
|||
|
|||
Difference between date and time
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)*2424,"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 "Joshua" wrote in message ... 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(C324,"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 "Bernard Liengme" wrote: =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 "Joshua" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|