Calculating Time Duration

[Avanz]

Hi Everyone,

I am so happy to have found this forum. I will try and explain my question as best as I can, but I’m a beginner user so please feel free to ask me questions to explain anything further.

At work I am meant to calculate the durations between Arrival & Departures times for certain items. I am able to easily do this by Changing the times to hh:mm AM/PM and then using =(Departure-Arrival) to get the duration.

However, I struggle when the Arrival times in the sheet is different from “Given Times”.

So sometimes the goods come early or later than given times. If they arrive early I have to calculate the duration between Given time - departure and if they arrive late, I have to calculate the duration between Arival time and departure.

I have been doing manually everyday for 50+ Items and absolutely sick of it. Is there any easier way to do this?

Thank You!

 

[etaf]

as we cannot see a sample set of data
how about

=IF ( Arrival time < Given time , Given time - departure , Arrival time and departure )

Not sure why you have to do this

I am able to easily do this by Changing the times to hh:mm AM/PM and then using =(Departure-Arrival) to get the duration.

Unless the time is in text format - But again, not being able to see the data , can not advise really

Note: Images are difficult to see , and also requires that I input all the data myself, which is very time consuming.

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC , then put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

 

[Avanz]

as we cannot see a sample set of data
how about

=IF ( Arrival time < Given time , Given time - departure , Arrival time and departure )

Not sure why you have to do this

Unless the time is in text format - But again, not being able to see the data , can not advise really

Note: Images are difficult to see , and also requires that I input all the data myself, which is very time consuming.

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC , then put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Here's a screenshot for reference

 

[etaf]

doesnt help a lot - can see cell references
also you have 1 column with Given time
but 2 columns for Arrival and 2 for Departure

As i say

=IF ( Arrival time < Given time , Given time - departure , Arrival time and departure )

Also

If they arrive early I have to calculate the duration between Given time - departure and if they arrive late, I have to calculate the duration between Arival time and departure.

looking at the row with 1:15
given time 9am - arrival time 7 am - so as early - then use Given time - departure

given time is 9am, departure is 9:15 am - so 0:15

so no idea how you come to the time you have

can you explain further and also use XL2BB as I linked to , or put the sheet on a share with an explanation and manually add examples so we know how you calculate

at the moment i dont see it - BUT maybe i'm missing something obvious

 

[Avanz]

doesnt help a lot - can see cell references
also you have 1 column with Given time
but 2 columns for Arrival and 2 for Departure

As i say


Also


looking at the row with 1:15
given time 9am - arrival time 7 am - so as early - then use Given time - departure

given time is 9am, departure is 9:15 am - so 0:15

so no idea how you come to the time you have

can you explain further and also use XL2BB as I linked to , or put the sheet on a share with an explanation and manually add examples so we know how you calculate

at the moment i dont see it - BUT maybe i'm missing something obvious

Hi sorry about that, I'll try to add the sheet.

1.the timeslot columns are meant to be "given times"

2. The 1:15 is a over all total time of both sim a (total time) + Sim b (total time)

3. I am also meant to calculate the overall total time in decimals.

book1.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Date	Item Pick	Timeslot (Given Time)	Arrival	Departure	Item Drop	Timeslot	Arrival	Departure	TOTAL TIME
2	30/05/2022	SIM A	09:00 AM	09:00 AM	09:45 PM	SIM B	11:00 AM	11:00 AM	12:30 AM	1.75
3	30/05/2022	SIM A	09:00 AM	09:15 AM	10:00 AM	SIM B	11:00 AM	10:45 AM	12:00 PM	2
4	30/05/2022	SIM A	08:00 AM	08:30 AM	09:00 AM	SIM B	10:00 AM	10:00 AM	11:45 AM
5	30/05/2022	SIM A	09:00 AM	08:30 AM	10:00 AM	SIM B	12:00 PM	11:45 AM	2:00 PM	3
6	30/05/2022	SIM A	08:00 AM	08:15 AM	09:20 AM	SIM B	10:00 AM	10:00 AM	12:00 PM
7	30/05/2022	SIM A	08:00 AM	08:00 AM	10:20 AM	SIM B	4:00 PM	4:00 PM	5:00 PM
8	30/05/2022	SIM A	09:00 AM	07:00 AM	09:15 AM	SIM B	12:00 PM	11:00 AM	1:00 PM
9	30/05/2022	SIM A	10:00 AM	10:45 AM	11:30 AM	SIM B	12:40 PM	1:00 PM	3:00 PM
10	30/05/2022	SIM A	10:00 AM	11:30 AM	12:30 AM	SIM B	1:20 PM	1:30 PM	3:40 PM
Sheet1

Cell Formulas
Range	Formula
J2	J2	=(0.75+1)
J3	J3	=(1+1)
J5	J5	=(1+2)

 

[etaf]

the 1:15 seems to have disappeared now
i'm really sorry , i am trying to help you

but i'm not following your example , which seems to have different results in column J - total time

can you explain how you do the calculations manually - using cell reference and a bit of explanation
for each row

in row 2 you have 0.75+1
how did you work those out and why

 

[Avanz]

the 1:15 seems to have disappeared now
i'm really sorry , i am trying to help you

but i'm not following your example , which seems to have different results in column J - total time

can you explain how you do the calculations manually - using cell reference and a bit of explanation
for each row

in row 2 you have 0.75+1
how did you work those out and why

Hi,

Its okay, I understand, also I seem to have figured it out.

=Depart-MAX(arrival,timeslot)

 

[etaf]

ok, glad you have it sorted now