Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enter Standard Time for H:MM Formula
To compute time spent on hourly projects, I need to know formula that will
allow me to enter standard time and have it find the difference ie/ (Start)A1: 4:00 (End)A2: 6:48 (Total)A3: 2.8 It sounds so easy just subtract the time and divide the Minutes by 60 but I just cant get it. Plz help! Thank You. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enter Standard Time for H:MM Formula
I've used this before
(Start)A1: 4:00 (End)A2: 6:48 (Total)A3: =IF(ISBLANK(A2),"",(A2-A1)*24) Cells A1 and A2 are using a time type 13:30 format Paul D "Benz" wrote in message ... : To compute time spent on hourly projects, I need to know formula that will : allow me to enter standard time and have it find the difference : : ie/ : (Start)A1: 4:00 : (End)A2: 6:48 : (Total)A3: 2.8 : : It sounds so easy just subtract the time and divide the Minutes by 60 but I : just cant get it. Plz help! Thank You. : : : |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enter Standard Time for H:MM Formula
a3: = a2-A1
format the cell as [mm]:00 for the time difference in minutes. the time you show is in hours and minutes, so to show hours and minutes, format as [h]:00 -- Regards, Tom Ogilvy "Benz" wrote: To compute time spent on hourly projects, I need to know formula that will allow me to enter standard time and have it find the difference ie/ (Start)A1: 4:00 (End)A2: 6:48 (Total)A3: 2.8 It sounds so easy just subtract the time and divide the Minutes by 60 but I just cant get it. Plz help! Thank You. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enter Standard Time for H:MM Formula
=A2-A1+(A1A2)
format as time |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enter Standard Time for H:MM Formula
A1 and A2 can use any time format. This will give you hours with a decimal.
so 30 minutes would be .5. If you wanted minutes (and it isn't clear what you want), then just multiply by 60. =IF(ISBLANK(A2),"",(A2-A1)*24*60) Note that time is stored as a decimal portion of a 24 hour day. -- Regards, Tom Ogilvy "PaulD" wrote: I've used this before (Start)A1: 4:00 (End)A2: 6:48 (Total)A3: =IF(ISBLANK(A2),"",(A2-A1)*24) Cells A1 and A2 are using a time type 13:30 format Paul D "Benz" wrote in message ... : To compute time spent on hourly projects, I need to know formula that will : allow me to enter standard time and have it find the difference : : ie/ : (Start)A1: 4:00 : (End)A2: 6:48 : (Total)A3: 2.8 : : It sounds so easy just subtract the time and divide the Minutes by 60 but I : just cant get it. Plz help! Thank You. : : : |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enter Standard Time for H:MM Formula
When I enter in this formula it gives me 0:00. So the easy part is the the
hours, the part that never comes out right is the minutes (48/60) to give a fraction of an hour. Does that kind of make sense? I'm not that expereinced with Excel so bare with me here. "Tom Ogilvy" wrote: A1 and A2 can use any time format. This will give you hours with a decimal. so 30 minutes would be .5. If you wanted minutes (and it isn't clear what you want), then just multiply by 60. =IF(ISBLANK(A2),"",(A2-A1)*24*60) Note that time is stored as a decimal portion of a 24 hour day. -- Regards, Tom Ogilvy "PaulD" wrote: I've used this before (Start)A1: 4:00 (End)A2: 6:48 (Total)A3: =IF(ISBLANK(A2),"",(A2-A1)*24) Cells A1 and A2 are using a time type 13:30 format Paul D "Benz" wrote in message ... : To compute time spent on hourly projects, I need to know formula that will : allow me to enter standard time and have it find the difference : : ie/ : (Start)A1: 4:00 : (End)A2: 6:48 : (Total)A3: 2.8 : : It sounds so easy just subtract the time and divide the Minutes by 60 but I : just cant get it. Plz help! Thank You. : : : |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enter Standard Time for H:MM Formula
That is certainly more robust, handling differences across midnight as well.
Similarly the more compact: =Mod(A2-A1,1) format as [h]:mm I would think. -- Regards, Tom Ogilvy "excelent" wrote: =A2-A1+(A1A2) format as time |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enter Standard Time for H:MM Formula
sorry for the error on those formats
[mm]:00 should be [mm]:ss and [h]:00 should be [h]:mm -- Regards, Tom Ogilvy "Tom Ogilvy" wrote: a3: = a2-A1 format the cell as [mm]:00 for the time difference in minutes. the time you show is in hours and minutes, so to show hours and minutes, format as [h]:00 -- Regards, Tom Ogilvy "Benz" wrote: To compute time spent on hourly projects, I need to know formula that will allow me to enter standard time and have it find the difference ie/ (Start)A1: 4:00 (End)A2: 6:48 (Total)A3: 2.8 It sounds so easy just subtract the time and divide the Minutes by 60 but I just cant get it. Plz help! Thank You. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enter Standard Time for H:MM Formula
It's giving me 2.48
"Tom Ogilvy" wrote: That is certainly more robust, handling differences across midnight as well. Similarly the more compact: =Mod(A2-A1,1) format as [h]:mm I would think. -- Regards, Tom Ogilvy "excelent" wrote: =A2-A1+(A1A2) format as time |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enter Standard Time for H:MM Formula
If I use PaulD's original formula with the values you show stored as dates
(A1 and A2), and format A3 as General I get: 2.8 -- Regards, Tom Ogilvy "Benz" wrote: When I enter in this formula it gives me 0:00. So the easy part is the the hours, the part that never comes out right is the minutes (48/60) to give a fraction of an hour. Does that kind of make sense? I'm not that expereinced with Excel so bare with me here. "Tom Ogilvy" wrote: A1 and A2 can use any time format. This will give you hours with a decimal. so 30 minutes would be .5. If you wanted minutes (and it isn't clear what you want), then just multiply by 60. =IF(ISBLANK(A2),"",(A2-A1)*24*60) Note that time is stored as a decimal portion of a 24 hour day. -- Regards, Tom Ogilvy "PaulD" wrote: I've used this before (Start)A1: 4:00 (End)A2: 6:48 (Total)A3: =IF(ISBLANK(A2),"",(A2-A1)*24) Cells A1 and A2 are using a time type 13:30 format Paul D "Benz" wrote in message ... : To compute time spent on hourly projects, I need to know formula that will : allow me to enter standard time and have it find the difference : : ie/ : (Start)A1: 4:00 : (End)A2: 6:48 : (Total)A3: 2.8 : : It sounds so easy just subtract the time and divide the Minutes by 60 but I : just cant get it. Plz help! Thank You. : : : |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enter Standard Time for H:MM Formula
Look again,
It gives me 2:48, 2 hours and 48 minutes. -- Regards, Tom Ogilvy "Benz" wrote: It's giving me 2.48 "Tom Ogilvy" wrote: That is certainly more robust, handling differences across midnight as well. Similarly the more compact: =Mod(A2-A1,1) format as [h]:mm I would think. -- Regards, Tom Ogilvy "excelent" wrote: =A2-A1+(A1A2) format as time |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enter Standard Time for H:MM Formula
Unless u realy want minutes as dec.
=(TIME(A2-A1+(A1A2))&","&(MINUTE(A2-A1+(A1A2))/60*100))*1 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enter Standard Time for H:MM Formula
format as numbers
"excelent" skrev: Unless u realy want minutes as dec. =(TIME(A2-A1+(A1A2))&","&(MINUTE(A2-A1+(A1A2))/60*100))*1 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enter Standard Time for H:MM Formula
It gives me an error message when I enter this.
"excelent" wrote: Unless u realy want minutes as dec. =(TIME(A2-A1+(A1A2))&","&(MINUTE(A2-A1+(A1A2))/60*100))*1 |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enter Standard Time for H:MM Formula
Sry. was in dannish
=(HOUR(A2-A1+(A1A2))&","&(MINUTE(A2-A1+(A1A2))/60*100))*1 "Benz" skrev: It gives me an error message when I enter this. "excelent" wrote: Unless u realy want minutes as dec. =(TIME(A2-A1+(A1A2))&","&(MINUTE(A2-A1+(A1A2))/60*100))*1 |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enter Standard Time for H:MM Formula
Am I doing something wrong? I've tried everything everyone of you have so
nicely suggested and nothing has worked. "Benz" wrote: To compute time spent on hourly projects, I need to know formula that will allow me to enter standard time and have it find the difference ie/ (Start)A1: 4:00 (End)A2: 6:48 (Total)A3: 2.8 It sounds so easy just subtract the time and divide the Minutes by 60 but I just cant get it. Plz help! Thank You. |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enter Standard Time for H:MM Formula
he probably meant this:
=(HOUR(A2-A1+(A1A2))&"."&(MINUTE(A2-A1+(A1A2))/60*100))*1 change the "." to "," or whatever your decimal separator is. However, the Mod(A2-A1,1) or A2-A2+(A1A2) either formatted as general should be sufficient to return the same result). -- Regards, Tom Ogilvy "Benz" wrote: It gives me an error message when I enter this. "excelent" wrote: Unless u realy want minutes as dec. =(TIME(A2-A1+(A1A2))&","&(MINUTE(A2-A1+(A1A2))/60*100))*1 |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enter Standard Time for H:MM Formula
the comma will still give problems anywhere comma isn't the decimal
separator. the Danish word for Hour is Time? Interesting. -- Regards, Tom Ogilvy "excelent" wrote: Sry. was in dannish =(HOUR(A2-A1+(A1A2))&","&(MINUTE(A2-A1+(A1A2))/60*100))*1 "Benz" skrev: It gives me an error message when I enter this. "excelent" wrote: Unless u realy want minutes as dec. =(TIME(A2-A1+(A1A2))&","&(MINUTE(A2-A1+(A1A2))/60*100))*1 |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enter Standard Time for H:MM Formula
|
#20
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enter Standard Time for H:MM Formula
I sent you an e-mail from benzNbent@hotmail
"Tom Ogilvy" wrote: Send me a sample worksheet to (with a valid return address) or give me your email and I will send you something with all of them working. -- Regards, Tom Ogilvy "Benz" wrote: Am I doing something wrong? I've tried everything everyone of you have so nicely suggested and nothing has worked. "Benz" wrote: To compute time spent on hourly projects, I need to know formula that will allow me to enter standard time and have it find the difference ie/ (Start)A1: 4:00 (End)A2: 6:48 (Total)A3: 2.8 It sounds so easy just subtract the time and divide the Minutes by 60 but I just cant get it. Plz help! Thank You. |
#21
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enter Standard Time for H:MM Formula
Just for completeness:
And I replied. You responded that what I sent appeared satisfactory. -- Regards, Tom Ogilvy "Benz" wrote: I sent you an e-mail from benzNbent@hotmail "Tom Ogilvy" wrote: Send me a sample worksheet to (with a valid return address) or give me your email and I will send you something with all of them working. -- Regards, Tom Ogilvy "Benz" wrote: Am I doing something wrong? I've tried everything everyone of you have so nicely suggested and nothing has worked. "Benz" wrote: To compute time spent on hourly projects, I need to know formula that will allow me to enter standard time and have it find the difference ie/ (Start)A1: 4:00 (End)A2: 6:48 (Total)A3: 2.8 It sounds so easy just subtract the time and divide the Minutes by 60 but I just cant get it. Plz help! Thank You. |
#22
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enter Standard Time for H:MM Formula
Yes, You did a wonderful job in helping me. :)
"Tom Ogilvy" wrote: Just for completeness: And I replied. You responded that what I sent appeared satisfactory. -- Regards, Tom Ogilvy "Benz" wrote: I sent you an e-mail from benzNbent@hotmail "Tom Ogilvy" wrote: Send me a sample worksheet to (with a valid return address) or give me your email and I will send you something with all of them working. -- Regards, Tom Ogilvy "Benz" wrote: Am I doing something wrong? I've tried everything everyone of you have so nicely suggested and nothing has worked. "Benz" wrote: To compute time spent on hourly projects, I need to know formula that will allow me to enter standard time and have it find the difference ie/ (Start)A1: 4:00 (End)A2: 6:48 (Total)A3: 2.8 It sounds so easy just subtract the time and divide the Minutes by 60 but I just cant get it. Plz help! Thank You. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting Standard Time to Military Time in Excel | Excel Discussion (Misc queries) | |||
Converting standard time to military time | Excel Worksheet Functions | |||
how can we enter at the same time maintain a formula in a cell? | Excel Discussion (Misc queries) | |||
formula for converting military time to standard time, etc | Excel Discussion (Misc queries) | |||
How do I enter a time formula into a worksheet that determines if. | Excel Discussion (Misc queries) |