|
Date/Time parsing and calculation
I'm struggling with this one and would appreciate some help please. I
have a Start Time fixed in C3, 10:20, in Custom format hh:mm. IOW, getting on for half past ten in the morning. In the body of the worksheet I have Col A Col B Filename Time from Start ----------------------------------- --------------- 20050813-105501-KennetAvonJoins.JPG 20050813-112231-ReadingHenley.JPG 20050813-114837-Sonning Lock.JPG etc Each filename is prefixed with date and time, e.g. 20050813-105501 is about 10:55 on 13th August 2005 (I'm ignoring seconds). I want to calculate and enter the times from start in Col B, like this: Col A Col B Filename Time from Start ----------------------------------- --------------- 20050813-105501-KennetAvonJoins.JPG 0:35 20050813-112231-ReadingHenley.JPG 1:02 20050813-114837-Sonning Lock.JPG 1:46 etc etc Could someone help me derive the correct formula please? -- Terry, West Sussex, UK |
Date/Time parsing and calculation
Try:
=TIME(MID(A1,10,2),MID(A1,12,2),0)-$C$3 "Terry Pinnell" wrote: I'm struggling with this one and would appreciate some help please. I have a Start Time fixed in C3, 10:20, in Custom format hh:mm. IOW, getting on for half past ten in the morning. In the body of the worksheet I have Col A Col B Filename Time from Start ----------------------------------- --------------- 20050813-105501-KennetAvonJoins.JPG 20050813-112231-ReadingHenley.JPG 20050813-114837-Sonning Lock.JPG etc Each filename is prefixed with date and time, e.g. 20050813-105501 is about 10:55 on 13th August 2005 (I'm ignoring seconds). I want to calculate and enter the times from start in Col B, like this: Col A Col B Filename Time from Start ----------------------------------- --------------- 20050813-105501-KennetAvonJoins.JPG 0:35 20050813-112231-ReadingHenley.JPG 1:02 20050813-114837-Sonning Lock.JPG 1:46 etc etc Could someone help me derive the correct formula please? -- Terry, West Sussex, UK |
Date/Time parsing and calculation
Terry Pinnell wrote:
I'm struggling with this one and would appreciate some help please. I have a Start Time fixed in C3, 10:20, in Custom format hh:mm. IOW, getting on for half past ten in the morning. In the body of the worksheet I have Col A Col B Filename Time from Start ----------------------------------- --------------- 20050813-105501-KennetAvonJoins.JPG 20050813-112231-ReadingHenley.JPG 20050813-114837-Sonning Lock.JPG etc Each filename is prefixed with date and time, e.g. 20050813-105501 is about 10:55 on 13th August 2005 (I'm ignoring seconds). I want to calculate and enter the times from start in Col B, like this: Col A Col B Filename Time from Start ----------------------------------- --------------- 20050813-105501-KennetAvonJoins.JPG 0:35 20050813-112231-ReadingHenley.JPG 1:02 20050813-114837-Sonning Lock.JPG 1:46 etc etc Could someone help me derive the correct formula please? Pleased to say I seem to have managed to come up with a solution myself. If the first row is 15, then a formula that works is: =(MID(A15,10,2)&":"&MID(A15,12,2))-$C$3 I found that formatting that as 'Time' gave the correct result. I expect there's a neater solution though! -- Terry, West Sussex, UK |
Date/Time parsing and calculation
Terry Pinnell wrote:
Terry Pinnell wrote: I'm struggling with this one and would appreciate some help please. I have a Start Time fixed in C3, 10:20, in Custom format hh:mm. IOW, getting on for half past ten in the morning. In the body of the worksheet I have Col A Col B Filename Time from Start ----------------------------------- --------------- 20050813-105501-KennetAvonJoins.JPG 20050813-112231-ReadingHenley.JPG 20050813-114837-Sonning Lock.JPG etc Each filename is prefixed with date and time, e.g. 20050813-105501 is about 10:55 on 13th August 2005 (I'm ignoring seconds). I want to calculate and enter the times from start in Col B, like this: Col A Col B Filename Time from Start ----------------------------------- --------------- 20050813-105501-KennetAvonJoins.JPG 0:35 20050813-112231-ReadingHenley.JPG 1:02 20050813-114837-Sonning Lock.JPG 1:46 etc etc Could someone help me derive the correct formula please? Pleased to say I seem to have managed to come up with a solution myself. If the first row is 15, then a formula that works is: =(MID(A15,10,2)&":"&MID(A15,12,2))-$C$3 I found that formatting that as 'Time' gave the correct result. I expect there's a neater solution though! However, the next bit still has me head-scratching. How do I turn that result of say 0:18 into hours, 0.300, (or 1:02 into 1.033, etc) so that I can do further calculations? -- Terry, West Sussex, UK |
Date/Time parsing and calculation
=B1*24
"Terry Pinnell" wrote: Terry Pinnell wrote: Terry Pinnell wrote: I'm struggling with this one and would appreciate some help please. I have a Start Time fixed in C3, 10:20, in Custom format hh:mm. IOW, getting on for half past ten in the morning. In the body of the worksheet I have Col A Col B Filename Time from Start ----------------------------------- --------------- 20050813-105501-KennetAvonJoins.JPG 20050813-112231-ReadingHenley.JPG 20050813-114837-Sonning Lock.JPG etc Each filename is prefixed with date and time, e.g. 20050813-105501 is about 10:55 on 13th August 2005 (I'm ignoring seconds). I want to calculate and enter the times from start in Col B, like this: Col A Col B Filename Time from Start ----------------------------------- --------------- 20050813-105501-KennetAvonJoins.JPG 0:35 20050813-112231-ReadingHenley.JPG 1:02 20050813-114837-Sonning Lock.JPG 1:46 etc etc Could someone help me derive the correct formula please? Pleased to say I seem to have managed to come up with a solution myself. If the first row is 15, then a formula that works is: =(MID(A15,10,2)&":"&MID(A15,12,2))-$C$3 I found that formatting that as 'Time' gave the correct result. I expect there's a neater solution though! However, the next bit still has me head-scratching. How do I turn that result of say 0:18 into hours, 0.300, (or 1:02 into 1.033, etc) so that I can do further calculations? -- Terry, West Sussex, UK |
Date/Time parsing and calculation
Excellent, thanks, works fine! But why? -- Terry, West Sussex, UK ==================== Toppers wrote: =B1*24 "Terry Pinnell" wrote: Terry Pinnell wrote: Terry Pinnell wrote: I'm struggling with this one and would appreciate some help please. I have a Start Time fixed in C3, 10:20, in Custom format hh:mm. IOW, getting on for half past ten in the morning. In the body of the worksheet I have Col A Col B Filename Time from Start ----------------------------------- --------------- 20050813-105501-KennetAvonJoins.JPG 20050813-112231-ReadingHenley.JPG 20050813-114837-Sonning Lock.JPG etc Each filename is prefixed with date and time, e.g. 20050813-105501 is about 10:55 on 13th August 2005 (I'm ignoring seconds). I want to calculate and enter the times from start in Col B, like this: Col A Col B Filename Time from Start ----------------------------------- --------------- 20050813-105501-KennetAvonJoins.JPG 0:35 20050813-112231-ReadingHenley.JPG 1:02 20050813-114837-Sonning Lock.JPG 1:46 etc etc Could someone help me derive the correct formula please? Pleased to say I seem to have managed to come up with a solution myself. If the first row is 15, then a formula that works is: =(MID(A15,10,2)&":"&MID(A15,12,2))-$C$3 I found that formatting that as 'Time' gave the correct result. I expect there's a neater solution though! However, the next bit still has me head-scratching. How do I turn that result of say 0:18 into hours, 0.300, (or 1:02 into 1.033, etc) so that I can do further calculations? -- Terry, West Sussex, UK |
Date/Time parsing and calculation
Thank you, much neater than mine!
-- Terry, West Sussex, UK ==================== Toppers wrote: Try: =TIME(MID(A1,10,2),MID(A1,12,2),0)-$C$3 "Terry Pinnell" wrote: I'm struggling with this one and would appreciate some help please. I have a Start Time fixed in C3, 10:20, in Custom format hh:mm. IOW, getting on for half past ten in the morning. In the body of the worksheet I have Col A Col B Filename Time from Start ----------------------------------- --------------- 20050813-105501-KennetAvonJoins.JPG 20050813-112231-ReadingHenley.JPG 20050813-114837-Sonning Lock.JPG etc Each filename is prefixed with date and time, e.g. 20050813-105501 is about 10:55 on 13th August 2005 (I'm ignoring seconds). I want to calculate and enter the times from start in Col B, like this: Col A Col B Filename Time from Start ----------------------------------- --------------- 20050813-105501-KennetAvonJoins.JPG 0:35 20050813-112231-ReadingHenley.JPG 1:02 20050813-114837-Sonning Lock.JPG 1:46 etc etc Could someone help me derive the correct formula please? -- Terry, West Sussex, UK |
Date/Time parsing and calculation
Times are held as decimal numbers with 24 hours=1 and 12 hours=0.5 so
multiplying by 24 converts hh:mm to the decimal equivalent. "Terry Pinnell" wrote: Excellent, thanks, works fine! But why? -- Terry, West Sussex, UK ==================== Toppers wrote: =B1*24 "Terry Pinnell" wrote: Terry Pinnell wrote: Terry Pinnell wrote: I'm struggling with this one and would appreciate some help please. I have a Start Time fixed in C3, 10:20, in Custom format hh:mm. IOW, getting on for half past ten in the morning. In the body of the worksheet I have Col A Col B Filename Time from Start ----------------------------------- --------------- 20050813-105501-KennetAvonJoins.JPG 20050813-112231-ReadingHenley.JPG 20050813-114837-Sonning Lock.JPG etc Each filename is prefixed with date and time, e.g. 20050813-105501 is about 10:55 on 13th August 2005 (I'm ignoring seconds). I want to calculate and enter the times from start in Col B, like this: Col A Col B Filename Time from Start ----------------------------------- --------------- 20050813-105501-KennetAvonJoins.JPG 0:35 20050813-112231-ReadingHenley.JPG 1:02 20050813-114837-Sonning Lock.JPG 1:46 etc etc Could someone help me derive the correct formula please? Pleased to say I seem to have managed to come up with a solution myself. If the first row is 15, then a formula that works is: =(MID(A15,10,2)&":"&MID(A15,12,2))-$C$3 I found that formatting that as 'Time' gave the correct result. I expect there's a neater solution though! However, the next bit still has me head-scratching. How do I turn that result of say 0:18 into hours, 0.300, (or 1:02 into 1.033, etc) so that I can do further calculations? -- Terry, West Sussex, UK |
Date/Time parsing and calculation
Thanks.
-- Terry, West Sussex, UK ==================== Toppers wrote: Times are held as decimal numbers with 24 hours=1 and 12 hours=0.5 so multiplying by 24 converts hh:mm to the decimal equivalent. "Terry Pinnell" wrote: Excellent, thanks, works fine! But why? -- Terry, West Sussex, UK ==================== Toppers wrote: =B1*24 "Terry Pinnell" wrote: Terry Pinnell wrote: Terry Pinnell wrote: I'm struggling with this one and would appreciate some help please. I have a Start Time fixed in C3, 10:20, in Custom format hh:mm. IOW, getting on for half past ten in the morning. In the body of the worksheet I have Col A Col B Filename Time from Start ----------------------------------- --------------- 20050813-105501-KennetAvonJoins.JPG 20050813-112231-ReadingHenley.JPG 20050813-114837-Sonning Lock.JPG etc Each filename is prefixed with date and time, e.g. 20050813-105501 is about 10:55 on 13th August 2005 (I'm ignoring seconds). I want to calculate and enter the times from start in Col B, like this: Col A Col B Filename Time from Start ----------------------------------- --------------- 20050813-105501-KennetAvonJoins.JPG 0:35 20050813-112231-ReadingHenley.JPG 1:02 20050813-114837-Sonning Lock.JPG 1:46 etc etc Could someone help me derive the correct formula please? Pleased to say I seem to have managed to come up with a solution myself. If the first row is 15, then a formula that works is: =(MID(A15,10,2)&":"&MID(A15,12,2))-$C$3 I found that formatting that as 'Time' gave the correct result. I expect there's a neater solution though! However, the next bit still has me head-scratching. How do I turn that result of say 0:18 into hours, 0.300, (or 1:02 into 1.033, etc) so that I can do further calculations? -- Terry, West Sussex, UK |
| All times are GMT +1. The time now is 04:49 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com