Hi everyone,
here is what i'm trying to achieve:
A1: 8:00-15:00 *7hours*
A2: 13:00-17:00 *4hours*
A3=11 *total hour of A1+A2*
thanks in advance!
Hi everyone,
here is what i'm trying to achieve:
A1: 8:00-15:00 *7hours*
A2: 13:00-17:00 *4hours*
A3=11 *total hour of A1+A2*
thanks in advance!
Helo,
On B1, paste this formula
Drag it down to B2, then you can add B1 to B2 normally, the result will be 11 (your expected A3)Please Login or Register to view this content.
Note that you will have to format them in hh:mm.
(copy pasta from Ford)
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
Regards,
Lem
Is the content of A1 the whole "8:00-15:00 *7hours*"?
If you put the times themselves into single cells you can use simple maths, like B2-B1
If you have sentences in cells instead of numbers you'd need a more complicated formula, which could extract the numbers 7 and 4 (I assume?) from the cells. I recommend you put each time into a single cell (that would mean at least four cells to cover your example). Then the spreadsheet is much easier to maintain.
CC
If you feel really indebted please consider a donation to charity. My preferred charity is ActionAid but there are plenty of worthy alternatives.
Hi linzheng
You will be better of keeping the times in separate cells,ie
A1: 08:00 B1: 15:00 then C1: =B1-A1
But for your example, try: =(--RIGHT(A1,5))-(--LEFT(A1,4))+(--(RIGHT(A2,5))-(LEFT(A2,5))) Format cells as hh:mm
Regards Kevin
Merged Cells (They are the work of the devil!!!)
Hi guys,
Thanks heaps for these quick replies, the problem is I have to keep the in single cells.
The content of A1 is just "8:00-15:00".
The fomular Lemice provided solved the problem, but I'm having trouble summing them up cause some cells are empty and they are coming back as #VALUE!.
Thanks
Lin
I see, then you can change the formula into this
and paste it on B1. In case the cell is blank, this formula will return 0 (making it sum-able)Please Login or Register to view this content.
If you don't want to see it return as 0, change the 0 at the end of the formula to ""
And use SUM to sum them up (SUM function will ignore text)Please Login or Register to view this content.
That works perfectly.
Thank you.
Try this...
=IF(A1="","",MOD(MID(A1,FIND("-",A1)+1,4)-LEFT(A1,FIND("-",A1)-1),1))
Format as h:mm
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Those formulas will fail if the time rolls over past midnight.
23:00-3:00
Tony, you should change your formula to
=IF(A1="","",MOD(MID(A1,FIND("-",A1)+1,5)-LEFT(A1,FIND("-",A1)-1),1
If you enter 8:00-15:30 for example, it will be 7:03 instead of 7:30
And you are right, in case the time rolls overnight, my formula will return the wrong value while yours still return the correct result.
Another question is: is it possible to have the sum in the format of 7.5hrs instead of 07:30?
12:00 in time format is actually equal to 0.5 (half a day, 12/24), so if you multiply the results to 24, you can turn them into number, and you can properly sum them over 24 hours (Remember to format them as General / Number)
Same thing happens to 07:30, if you multiply it with 24, it will turn into 7.5 in General / Number format.
Do you want the "hrs" to be included?
If so try this:
=IF(A1="","",MOD(MID(A1,FIND("-",A1)+1,5)-LEFT(A1,FIND("-",A1)-1),1)*24&"hrs")
Or, for the result to be in decimal format without the "hrs":
=IF(A1="","",MOD(MID(A1,FIND("-",A1)+1,5)-LEFT(A1,FIND("-",A1)-1),1)*24)
Format both as General
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks