times past midnight not working properly

[thesilkster]

Good evening i have a sheet i have been working on for sometime now and i cannot figure out how to show the correct times passed midnight

column e for row 4 should show -00:20 and same with column e row 5 that should show
-01:00

can anybody help please

 

[Chihiro]

Normally I'd track date and time in same cell to do this type of calculation.

Also, your calculation for G & H was bit off (since 60+ min will be true for both G & H).

See attached.

FYI - in your current set up, you'd need to differentiate when D2>C2 and also when arrival is on same date vs. following date.

 

[thesilkster]

thanks for the quick reply Chihiro most appreciated i have just discovered a flaw if they turn up before booked time it shows up in the [TO BE INCLUDED +60 MIN] how would i show another column showing early =1 please

 

[Chihiro]

Can you upload the sample where you get error?

If you use my formula & set up, it should not happen except when they arrive more than 1 hour early. Since formula uses ABS() to look for absolute difference only (Early or late).

 

[thesilkster]

ok i can see that do you have any other ideas? sometimes the do arrive earlier i am going to put a pivot table on the back of this so if there was another column showing early that would be great

thanks

 

[Chihiro]

See attached.

I assumed that for Column G to I, you wanted to consider only late arrivals.

 

[thesilkster]

your assumption is correct thank you very much

 

[thesilkster]

Hi Chihiro i am having difficulty when pasting the data in the sheet I have attached the sheet the data is copied from the red tab into the green tab, the problem I am having is the data extract is not formatted you can see that all data is showing as "general", I also have to remove the duplicates from the Manifest No column on red tab? I have attached a copy of the data extract as it comes off the system...

any help is most appreciated

 

[Chihiro]

I'm not sure that I understand you completely.

Please explain each step and show expected output using sample data you attached.

 

[thesilkster]

hi the data from qrysubbytracker_xls needs to be copied daily into the Late collections sheet on the first tab (A:H)

• the data in the qrysubbytracker_xls need to have the duplicates taking out using the trip id as the unique key column (E)
• the data that's left needs to be copied into the first tab on the late's collection workbook. the headings should match (A:H)
• Column (J) is the input time it doesn't work? could be the formatting when I paste into the column (A:H)

I hope this clarifies

thanks for your persistence

 

[Chihiro]

There's several issues here.

1. Dates aren't date values, but texts.
   • You will need to convert it to actual date value at some stage
   • Can be done via Formula or VBA
2. Column F:H missing in qryOLCSubbyTracker
   • Will it be present in actual or is it populated by other means?
3. Loadby & DepartBy Time
   • As I stated originally, you'd need time and date in same cell to use formula I used
   • This can be done via formula (Helper Column)
   • Or adjust my formula to use separate cells for date & time (date + time)

How do you want point #1 taken care of? It's important as this will impact subsequent process.

 

[thesilkster]

point 1 can be done via formula, once the conversation is taken place it will be cut and pasted into the first tab (A:H)
point 2 can be done by a helper column after column H as this would not interfere with pasting part? I do agree with you about dd/mm/yy/ hh:ss it would make it so much easier unfortunately the system out puts date and time separate?

would you use concatenate?

cheers Chihiro

 

[Chihiro]

Here's what you can do.

Select Columns A:H in qryOLSubbyTracker and remove duplicates (or copy else where and do it).

Then add 5 helper columns.
To convert date text into date... [HelperLoadD] & [HelperDepartD] in attached.
=DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2))

To convert time text into time... [HelperLoadT] & [HelperDepartT] in attached.
=B2*1

Last helper is just copying value from Column E (for layout).

Copy result to late collections' "Input Data" sheet.

See attached.

Note: I see that input columns has only hours and not dates. You'd need both.

 

[thesilkster]

Hi iv been playing around with this for a few days still not coming out right I will have another go tomorrow, thanks for your help its appreciated. I will try and put a validation field in the input column i.e. dd/mm/yyyy hh:mm would that work? I will post the whole document up with real data in tomorrow when no past the data in from the helper column I take it you have to paste values? Thanks

 

[Chihiro]

Date is always tricky to deal with.

Most of the time, I specify date format to vendor (if it's software generated report), when they can't provide me with connection to backend.

...when no past the data in from the helper column I take it you have to paste values?

Not sure what you mean.

 

[thesilkster]

i have sorted it may i take this opportunity to say thank you very much for all your help