Ask Question Forum:
Model Library:2025-02-08 Updated:A.I. model is online for auto reply question page
C
O
M
P
U
T
E
R
2
8
Show
#
ASK
RECENT
←
- Underline
- Bold
- Italic
- Indent
- Step
- Bullet
- Quote
- Cut
- Copy
- Paste
- Table
- Spelling
- Find & Replace
- Undo
- Redo
- Link
- Attach
- Clear
- Code
Below area will not be traslated by Google,you can input code or other languages
Hint:If find spelling error, You need to correct it,1 by 1 or ignore it (code area won't be checked).
X-position of the mouse cursor
Y-position of the mouse cursor
Y-position of the mouse cursor
Testcursor
caretPos
Attachment:===
Asked by runnerjp2005
at 2024-08-01 03:32:42
ModifyPoint:500 Replies:12 POST_ID:828648USER_ID:11520
Topic:
Microsoft Excel Spreadsheet Software;;Microsoft Office Suite
I would like to display the start time and the end time with how many hours it took like this
08:57 - 09:22 (25 Mintues)
or
07:57 - 09:22 (1 Hour 25 Minutes)
How cna i do this if i have the following cells
Start time End time outage time
01/08/2013 08:57 01/08/2013 08:57 0:25:00
The start and end time fields are formatted as datetime
08:57 - 09:22 (25 Mintues)
or
07:57 - 09:22 (1 Hour 25 Minutes)
How cna i do this if i have the following cells
Start time End time outage time
01/08/2013 08:57 01/08/2013 08:57 0:25:00
The start and end time fields are formatted as datetime
Expert: duncanb7 replied at 2024-08-01 08:16:46
Thanks for your score and yahooooo"s text() reminder
Expert: byundt replied at 2024-08-01 05:28:24
Try using the TEXT function. Here are two formulas. The first gives the difference in minutes, while the second gives it in hours and minutes:
=TEXT(A2,"hh:mm") & " - " & TEXT(B2,"hh:mm") & " (" & TEXT(B2-A2,"[m]") & " Minutes)"
=TEXT(A2,"hh:mm") & " - " & TEXT(B2,"hh:mm") & " (" & TEXT(B2-A2,"h ""Hours and ""m ""Minutes)""")
=TEXT(A2,"hh:mm") & " - " & TEXT(B2,"hh:mm") & " (" & TEXT(B2-A2,"[m]") & " Minutes)"
=TEXT(A2,"hh:mm") & " - " & TEXT(B2,"hh:mm") & " (" & TEXT(B2-A2,"h ""Hours and ""m ""Minutes)""")
Accepted Solution
Expert: duncanb7 replied at 2024-08-01 04:54:31
250 points EXCELLENT
I think yahooooooo and I give you much information to you, just minor typing mistake or error
I think you can handle it
What your are talking about, which formula ? you have to use correctly on Text(....,"hh:mm"),
play around those excel command of text, hour, second that will easily solve it .
Be reminded
"=TEXT("9:1", "hh:ss")" --->09:00, you can practise or make example to see how to use text()
in a blank xls file
I think you can handle it
What your are talking about, which formula ? you have to use correctly on Text(....,"hh:mm"),
play around those excel command of text, hour, second that will easily solve it .
Be reminded
"=TEXT("9:1", "hh:ss")" --->09:00, you can practise or make example to see how to use text()
in a blank xls file
Author: runnerjp2005 replied at 2024-08-01 04:45:31
aLSO IF THE TIME IS 09:05:00 IT WILL SHOW IT AS 9:5
Author: runnerjp2005 replied at 2024-08-01 04:42:53
Works just about right duncanb7.... if the time goes over an hour will it display 1 hour ** Minutes???
Expert: duncanb7 replied at 2024-08-01 04:20:15
First work, sourceworkbook.xls
#####A###############B####################C#################
Start time End time difference time(in hour and in min)
01/08/2013 08:57:00 01/08/2013 09:59:08 =TEXT(HOUR(B2)-HOUR(A2) & ":" &MINUTE(B2)-MINUTE(A2) ,"hh:mm")
^ the above is on one work book.
On another work book it will have at A1
second workbook
########A#########
=HOUR([sourceworkbook.xls]Sheet1!$A$2) & ":" & MINUTE([sourceworkbook.xls]Sheet1!$A$2) & " - " & HOUR([sourceworkbook.xls]Sheet1!$B$2) & ":" & MINUTE([sourceworkbook.xls]Sheet1!$B$2) & " (" &(HOUR([sourceworkbook.xls]Sheet1!$C$2)*60+MINUTE([sourceworkbook.xls]Sheet1!$C$2) &" minutes)"
runnerjp2005, other minor change or typing mistake I think you can handle it
#####A###############B####################C#################
Start time End time difference time(in hour and in min)
01/08/2013 08:57:00 01/08/2013 09:59:08 =TEXT(HOUR(B2)-HOUR(A2) & ":" &MINUTE(B2)-MINUTE(A2) ,"hh:mm")
^ the above is on one work book.
On another work book it will have at A1
second workbook
########A#########
=HOUR([sourceworkbook.xls]Sheet1!$A$2) & ":" & MINUTE([sourceworkbook.xls]Sheet1!$A$2) & " - " & HOUR([sourceworkbook.xls]Sheet1!$B$2) & ":" & MINUTE([sourceworkbook.xls]Sheet1!$B$2) & " (" &(HOUR([sourceworkbook.xls]Sheet1!$C$2)*60+MINUTE([sourceworkbook.xls]Sheet1!$C$2) &" minutes)"
runnerjp2005, other minor change or typing mistake I think you can handle it
Expert: duncanb7 replied at 2024-08-01 04:15:32
Yes yahoo, get closer but I need some change
=HOUR([sourceworkbook.xlsx]Sheet1!$A$2) & ":" & MINUTE([sourceworkbook.xlsx]]Sheet1!$A$2) & " - " & HOUR([sourceworkbook.xlsx]]Sheet1!$B$2) & ":" & MINUTE([sourceworkbook.xlsx]]Sheet1!$B$2) & " (" &([sourceworkbook.xlsx]]Sheet1!$C$2*60+[sourceworkbook.xlsx]]Sheet1!$C$2 &" minutes)"
Please help to cross-check it
=HOUR([sourceworkbook.xlsx]Sheet1!$A$2) & ":" & MINUTE([sourceworkbook.xlsx]]Sheet1!$A$2) & " - " & HOUR([sourceworkbook.xlsx]]Sheet1!$B$2) & ":" & MINUTE([sourceworkbook.xlsx]]Sheet1!$B$2) & " (" &([sourceworkbook.xlsx]]Sheet1!$C$2*60+[sourceworkbook.xlsx]]Sheet1!$C$2 &" minutes)"
Please help to cross-check it
Assisted Solution
Expert: yahooooo replied at 2024-08-01 04:08:27
250 points EXCELLENT
paste it in another workbook and change 'sourceworkbook.xlsx' to you workbook name also change the sheet name from sheet1 to whatever you have in your workbook. It would be easiest if you upload your workbook. You don't need to count c2 value on your worksheet as it's counted in formula below ;)
=HOUR([sourceworkbook.xlsx]Sheet1!$A$2) & ":" & MINUTE([sourceworkbook.xlsx]]Sheet1!$A$2) & " - " & HOUR([sourceworkbook.xlsx]]Sheet1!$B$2) & ":" & MINUTE([sourceworkbook.xlsx]]Sheet1!$B$2) & " (" &TEXT([sourceworkbook.xlsx]]Sheet1!$B$2-[sourceworkbook.xlsx]]Sheet1!$A$2,"h:mm")& " minutes)"
=HOUR([sourceworkbook.xlsx]Sheet1!$A$2) & ":" & MINUTE([sourceworkbook.xlsx]]Sheet1!$A$2) & " - " & HOUR([sourceworkbook.xlsx]]Sheet1!$B$2) & ":" & MINUTE([sourceworkbook.xlsx]]Sheet1!$B$2) & " (" &TEXT([sourceworkbook.xlsx]]Sheet1!$B$2-[sourceworkbook.xlsx]]Sheet1!$A$2,"h:mm")& " minutes)"
Author: runnerjp2005 replied at 2024-08-01 03:58:17
#####A###############B####################C#################
Start time End time difference time(in hour and in min)
01/08/2013 08:57:00 01/08/2013 09:59:08 1:08
^ the above is on one work book.
On anouther work book a will have
########A#########
08:57 - 09:22 (25 Mintues)
I pull the start time dates like this ='thefolder/[Current%20Months%20Report.xls]Spredsheet'!$A$1
Start time End time difference time(in hour and in min)
01/08/2013 08:57:00 01/08/2013 09:59:08 1:08
^ the above is on one work book.
On anouther work book a will have
########A#########
08:57 - 09:22 (25 Mintues)
I pull the start time dates like this ='thefolder/[Current%20Months%20Report.xls]Spredsheet'!$A$1
Expert: duncanb7 replied at 2024-08-01 03:56:20
C2 formula for final if I understood what you need,
=TEXT(HOUR(B2)-HOUR(A2) & ":" & MINUTE(B2)-MINUTE(A2) & ":" & SECOND(B2)- SECOND(A2),"hh:mm:ss")
If you don't need second or ss , just take out it
y=TEXT(HOUR(B2)-HOUR(A2) & ":" & MINUTE(B2)-MINUTE(A2) ,"hh:mm")
=TEXT(HOUR(B2)-HOUR(A2) & ":" & MINUTE(B2)-MINUTE(A2) & ":" & SECOND(B2)- SECOND(A2),"hh:mm:ss")
If you don't need second or ss , just take out it
y=TEXT(HOUR(B2)-HOUR(A2) & ":" & MINUTE(B2)-MINUTE(A2) ,"hh:mm")
Expert: yahooooo replied at 2024-08-01 03:47:43
put in c2 assuming that a2 is start hour and b2 is end hour
=TEXT(B2-A2,"h:mm:ss")
=TEXT(B2-A2,"h:mm:ss")
Expert: duncanb7 replied at 2024-08-01 03:39:04
Could you write down what you want clearly ?
For example
#####A#############B###################C
Start time End time difference time(in hour and in min)
01/08/2013 08:57:00 01/08/2013 09:59:08 1:08
You want the formula for C2 cells for difference for End- start time, RIght ?
For example
#####A#############B###################C
Start time End time difference time(in hour and in min)
01/08/2013 08:57:00 01/08/2013 09:59:08 1:08
You want the formula for C2 cells for difference for End- start time, RIght ?
