Times in Excel
 

I have written a spreadsheet for work that works out the monthly invoice for one of the airlines we handle. Depending on ATA and ATD and length on grnd determines the different charges we have to invoice for i.e if <3hrs rate x if >3hrs rate y, if after 1800 + 25%. Everything works except that if it arrives before midnight say 22:10 and goes at 02:00 if I put in 02:00 it comes up with ###### (bad data) but if I put in 26:00 (i.e. 24:00 + 2) it works fine. Anybody know of a way around this, the reason being I'm not always there, and others around me can't seem to get it!

A quick fix is to format your inputs as day/time;
19/04/00 22:00
20/04/00 02:00
should give a result (4:00)


------------------
'I may disagree with what you have to say, but I will defend, to the death, your right to say it' - Voltaire

[This message has been edited by jfe117 (edited 19 April 2000).]

Spanners, I think JFE117 has got the idea - and he posted while I was looking at some spreadsheets I did.

However, I think if you go to Format/Cells and look at TIME you might find that the instructions you gave are 'bad data' 'cos you are 'asking' it to go BACK to 02:00 after having given it 22:10 or whatever. You do, therefore, need to IF the next day with a date, and the time. The cell format does in fact allow for that.

Hope that right.

Another possibility is to compare the times. If the finish time is numerically LESS than the start time add 24 hrs to it & then subtract the start time.

That should compute the correct period.

Example of the method:

Start at 2200, finish at 0200

0200 is < 2200, therefore add 24 hrs = 26 hrs.

Subtract 22 hrs from 26 hrs =4 hrs period

Afraid I'm too lazy tonight to turn the above into an Excel formula though!

Thanks all I'll give it a go tomorrow.