Search: | |||||

| ||||

Home » Forum » MS Office | RSS Feeds |

In the program I working on for handling call times, a situation

came up which turned up an error so I would like any suggestions you

have for this. It seems like something like this came up here in

discussion recently, but when I looked, I couldn't find it my files

of previous postings. If this is a repeat, my apologies, folks.

Since it's a 24-hour operation, a call started at 23:58:58 and

handling the call went past midnight, so the dispatch time was

listed as 00:00:23 (i.e. 23 seconds past midnight). So, as per my

own code which looks for confusion in the data, it got kicked onto

the Error List sheet, because it was calculated as a negative

timelapse (which is, of course, impossible). At this point, the

program is being beta tested so the supervisor and I talked about

this, so it will be hand corrected this particular month.

Of course, I'd like to get it corrected as soon as I can, so it

doesn't repeat. Any suggestions? If anyone needs to see the code I

have, I'll be glad to post it, but I thought the problem was already

pretty clear.

If the time is a normal date/time value it will not matter. The datetime

value is a decimal number of days since some long distant date that is it as

two parts and integer part for the day and a decimal part for the fraction

of a day.

If you use this variable you can subtract the start time from the end time

without problems. ten past midnight will have a higher integer value than

ten to midnight the day before. You only need to check that the call was

not completed before it started by checking end<start.

If you have reinvented the way to track time I suggest you rethink.

You should really provide more information. I.e. how are you entering these

times? Are they in cells as times (i.e. with colons)? I'll assume so.

Therefore your elapsed time calculation will be something like

=F5-F4

Seeing there is no date information in the date-time fields, you need to

compensate. Basically, you need the result modulo one day. I.e.

=MOD(F5-F4,1)

If you're holding the times in some other fashion, you'll need to go modulo

24 hours or one day, as appropriate.

I do see now that I left out important

information, but you solved it anyway. Your assumption that the

times are in cells as times with colons was correct, and I had

actually overlooked that I had stripped the date information out

intentionally because the data comes in 2 different forms from 2

different sources. Separating the DATE and time was a way to make

sure the data was in the same form for analysis.

So, David G.'s point about the original Excel values for dates

and times would have worked, except that I had removed the DATE

part, which I should have mentioned. However, the MOD part of the

formula that David S. directed me to seems to have solved the

problem. I've got to sift through the existing formula in order to

add that in, because I had written in error traps in the formula

itself, but that shouldn't be hard to figure out.

Thanks again to a great group. You did it again.

I can add that if you include the dates, the subtraction will work; i.e.

subtract the START date and time from the END date and time.

Use the function:

= ( EndDate + End Time ) - ( StartDate + StartTime )

the DATE has been stripped from this part of the data, because one

of the 2 data sources does NOT include a DATE--therefore in order

for the data to be consistent, I removed the the date from all of

these entries. Otherwise, your comment is a welcome one, thank you.

(I wonder is that would make it a "bad data day" instead of a "bad

hair day"? . . . heheh. . . . just kidding.)

If you have 23:58:58 in cell A1 and 00:00:23 in cell B1 enter the following

formula in C1:

=IF(A1>B1,(B1+24)-A1,B1-A1)

Will this work for you?

Again thanks for your thoughts, and I am considering the

possibilities of incorporating each of these ideas from all who have

responded. The latest problem I discovered is that some of the 'bad

data' errors include a null value (like when the system cannot

interpret the input) in the source, which get's outputed

as "00:00:00" for the dispatch value--which get's interpreted as

midnight, exactly.

So, using your formula would interpret a 'null' entry, which I

had hoped to kick to the error List, as a legit entry, so it would

undo the previous error traps.

With all that everyone has offered, though, my latest

attempt/thought has been to have a there supervisor make a judgement

on the time of the dispatch. If they listen to the call (which are

all recorded, since this is a 911 center) and find that the call's

dispatch time is legit and in fact it was dispatched exactly at

midnight "00:00:00", or a few seconds later, then with a button,

that time will be "honored" in the calculations. Otherwise, I can't

see anyway to filter the calculations, without undoing the whole

purpose of the error check.

One thing for sure, though, all the ideas which everyone can

kick out will be welcome to me, indeed, with thanks. I've said it

many times, but this is a great group and I read the postings every

day.

Didn't find what you were looking for?
Find more on Time difference calculations across midnight
Or get search suggestion and latest updates.

Related Topics:

- Time Difference for Page Creation time
- Time taken - Calculation
- time calculation
- To calculate the time difference between two days
- Finding difference in time
- Disabling Formulas, Not calculation
- Pivot Charts - Custom Calculations
- Pivot Tables Calculations
- adds =round(cell references,5) to allocate the calculation to 5 places
- User Function - is optional calculation possibe?
- DISK SPACE CALCULATION
- Constructors in calculation
- Display character which appears number of time equal half of maximum time
- Best way to load multiple images across multiple classes in an appl
- Sharing Excel Across the Network
- Problem invoking ejbs across servers
- Common header across all my aspx files
- Tx propagation across multiple weblogic servers
- Hibernate mapping across multiple databases
- Difference b/w java beans & enterprise java beans
- difference between ArrayList & Vector
- difference between strings, classes, and objects
- Difference between JNI and CORBA ?
- Difference between .aspx file and .aspx.vb file
- Differences between API's