Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with time conversions
I have a small problem that I can't seem to solve if anyone can help
it would be appreciated. Time cells are h:mm AM/PM decimal cells are 0.00 B14 = AM time(eg: 7:15 AM) C14 = AM time later than B14 (eg: 9:15 AM) D14 = PM time (eg: 2:30 PM) E14 = PM time later than D14 (eg: 7:15 PM) I need to make B15 tell me that if B14 is earlier than 8am the difference between 8am and the time entered and give me the answer in a decimal number. In this case the answer is 0.75 I then need to make B16 tell me the difference between 8am and the time in C14 in a decimal number. In this case the answer is 1.25. I also need D15 tell me that if E14 is after 6pm the number of hours from 6pm to the time entered in decimal number. In this case the answer is 1.25 And likewise I need cell D16 to give me the time difference between cell D14 and E14 up to 6pm. So in this case the answer is 3.5. If there is any excel guru out there that can figure that out please feel free to solve away. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with time conversions
Logically, that didn't all flow, so this is what I discerned, you wanted specific decimal time values below the time stamps themselves. The first two breaking out around 8am, the second two breaking out around 6pm. If so, here are the formulas: B15 =IF(B14<TIME(8,0,0),(TIME(8,0,0)-B14)*24) C15 =(TIME(8,0,0)-C14)*-24 D15 =IF(E14TIME(18,0,0),(TIME(18,0,0)-D14)*24) E15 =(TIME(18,0,0)-E14)*-24 -- JBeaucaire ------------------------------------------------------------------------ JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=59502 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with time conversions
Slight correction:
7:15 AM 9:15 AM 2:30 PM 7:15 PM --0.75----1.25----3.5-----1.25 B15 =IF(B14<TIME(8,0,0),(TIME(8,0,0)-B14)*24) C15 =(C14-TIME(8,0,0))*24 D15 =IF(E14TIME(18,0,0),(TIME(18,0,0)-D14)*24) E15 =(E14-TIME(18,0,0))*24 -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "JBeaucaire" wrote: Logically, that didn't all flow, so this is what I discerned, you wanted specific decimal time values below the time stamps themselves. The first two breaking out around 8am, the second two breaking out around 6pm. If so, here are the formulas: B15 =IF(B14<TIME(8,0,0),(TIME(8,0,0)-B14)*24) C15 =(TIME(8,0,0)-C14)*-24 D15 =IF(E14TIME(18,0,0),(TIME(18,0,0)-D14)*24) E15 =(TIME(18,0,0)-E14)*-24 -- JBeaucaire ------------------------------------------------------------------------ JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=59502 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with time conversions
On Feb 6, 6:17*pm, JBeaucaire
wrote: Slight correction: 7:15 AM * 9:15 AM * 2:30 PM * 7:15 PM --0.75----1.25----3.5-----1.25 B15 * * =IF(B14<TIME(8,0,0),(TIME(8,0,0)-B14)*24) C15 * * =(C14-TIME(8,0,0))*24 D15 * * =IF(E14TIME(18,0,0),(TIME(18,0,0)-D14)*24) E15 * * =(E14-TIME(18,0,0))*24 -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "JBeaucaire" wrote: Logically, that didn't all flow, so this is what I discerned, you wanted specific decimal time values below the time stamps themselves. The first two breaking out around 8am, the second two breaking out around 6pm. If so, here are the formulas: B15 * * * *=IF(B14<TIME(8,0,0),(TIME(8,0,0)-B14)*24) C15 * * * *=(TIME(8,0,0)-C14)*-24 D15 * * * *=IF(E14TIME(18,0,0),(TIME(18,0,0)-D14)*24) E15 * * * *=(TIME(18,0,0)-E14)*-24 -- JBeaucaire ------------------------------------------------------------------------ JBeaucaire's Profile:http://www.thecodecage.com/forumz/member.php?userid=73 View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=59502- Hide quoted text - - Show quoted text - Thankyou you are an angel (oops "rocket scientist"). However the calculations in cell E15 gets all mucked up if the time goes past 6pm it shows the figure as a negative rather than a positive. I can correct this by removing the "-" sign from in front of the *24 but then when the time is less than 6pm I have to put the "-" sign back??? What you have given me I can work with though and no doubt some playing around will get me there. Thankyou thankyou thankyou |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with time conversions
You may have missed my followup formulas, but here's another set that you may
like better. It takes into account the fact that the 1st time may be after 8am, in which case a separate sum isn't needed, and the 4th time may be before 6pm, in which case a 4th sum isn't needed. In both instances you only need one time interval for the pairs, right? Like so: B15 =IF(B14<TIME(8,0,0),(TIME(8,0,0)-B14)*24,"") C15 =IF(B14=TIME(8,0,0),(C14-B14)*24,(C14-TIME(8,0,0))*24) D15 =IF(E14TIME(18,0,0),(TIME(18,0,0)-D14)*24,"") E15 =IF(E14<=TIME(18,0,0),(E14-D14)*24,(E14-TIME(18,0,0))*24) -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "WPP" wrote: On Feb 6, 6:17 pm, JBeaucaire wrote: Slight correction: 7:15 AM 9:15 AM 2:30 PM 7:15 PM --0.75----1.25----3.5-----1.25 B15 =IF(B14<TIME(8,0,0),(TIME(8,0,0)-B14)*24) C15 =(C14-TIME(8,0,0))*24 D15 =IF(E14TIME(18,0,0),(TIME(18,0,0)-D14)*24) E15 =(E14-TIME(18,0,0))*24 -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "JBeaucaire" wrote: Logically, that didn't all flow, so this is what I discerned, you wanted specific decimal time values below the time stamps themselves. The first two breaking out around 8am, the second two breaking out around 6pm. If so, here are the formulas: B15 =IF(B14<TIME(8,0,0),(TIME(8,0,0)-B14)*24) C15 =(TIME(8,0,0)-C14)*-24 D15 =IF(E14TIME(18,0,0),(TIME(18,0,0)-D14)*24) E15 =(TIME(18,0,0)-E14)*-24 -- JBeaucaire ------------------------------------------------------------------------ JBeaucaire's Profile:http://www.thecodecage.com/forumz/member.php?userid=73 View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=59502- Hide quoted text - - Show quoted text - Thankyou you are an angel (oops "rocket scientist"). However the calculations in cell E15 gets all mucked up if the time goes past 6pm it shows the figure as a negative rather than a positive. I can correct this by removing the "-" sign from in front of the *24 but then when the time is less than 6pm I have to put the "-" sign back??? What you have given me I can work with though and no doubt some playing around will get me there. Thankyou thankyou thankyou |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with time conversions
Thankyou thankyou thankyou Thankyou thankyou thankyou
Thankyou thankyou thankyou On Feb 6, 7:25*pm, JBeaucaire wrote: You may have missed my followup formulas, but here's another set that you may like better. It takes into account the fact that the 1st time may be after 8am, in which case a separate sum isn't needed, and the 4th time may be before 6pm, in which case a 4th sum isn't needed. In both instances you only need one time interval for the pairs, right? Like so: B15 =IF(B14<TIME(8,0,0),(TIME(8,0,0)-B14)*24,"") C15 =IF(B14=TIME(8,0,0),(C14-B14)*24,(C14-TIME(8,0,0))*24) D15 =IF(E14TIME(18,0,0),(TIME(18,0,0)-D14)*24,"") E15 =IF(E14<=TIME(18,0,0),(E14-D14)*24,(E14-TIME(18,0,0))*24) -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "WPP" wrote: On Feb 6, 6:17 pm, JBeaucaire wrote: Slight correction: 7:15 AM * 9:15 AM * 2:30 PM * 7:15 PM --0.75----1.25----3.5-----1.25 B15 * * =IF(B14<TIME(8,0,0),(TIME(8,0,0)-B14)*24) C15 * * =(C14-TIME(8,0,0))*24 D15 * * =IF(E14TIME(18,0,0),(TIME(18,0,0)-D14)*24) E15 * * =(E14-TIME(18,0,0))*24 -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "JBeaucaire" wrote: Logically, that didn't all flow, so this is what I discerned, you wanted specific decimal time values below the time stamps themselves. The first two breaking out around 8am, the second two breaking out around 6pm.. If so, here are the formulas: B15 * * * *=IF(B14<TIME(8,0,0),(TIME(8,0,0)-B14)*24) C15 * * * *=(TIME(8,0,0)-C14)*-24 D15 * * * *=IF(E14TIME(18,0,0),(TIME(18,0,0)-D14)*24) E15 * * * *=(TIME(18,0,0)-E14)*-24 -- JBeaucaire ------------------------------------------------------------------------ JBeaucaire's Profile:http://www.thecodecage.com/forumz/member.php?userid=73 View this thread:http://www.thecodecage.com/forumz/sh...p?t=59502-Hide quoted text - - Show quoted text - Thankyou you are an angel (oops "rocket scientist"). However the calculations in cell E15 gets all mucked up if the time goes past 6pm it shows the figure as a negative rather than a positive. I can correct this by removing the "-" sign from in front of the *24 but then when the time is less than 6pm I have to put the "-" sign back??? What you have given me I can work with though and no doubt some playing around will get me there. Thankyou thankyou thankyou- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
decimal time conversions | Excel Discussion (Misc queries) | |||
conversions | Excel Worksheet Functions | |||
Time conversions | Excel Worksheet Functions | |||
Conversions | Excel Discussion (Misc queries) | |||
Time calculations & text conversions | Excel Discussion (Misc queries) |