need help with Numbers formula.

The answer depends. A one-to-one equivalent would be

=FLOOR((TIMEVALUE(D18)−TIMEVALUE(C18))*1440,6)/60+IF(MOD((TIMEVALUE(D18)−TIMEVALUE(C18)),1)<>0,0.1,0)

In Excel, "times" have no dates. D18-C18 will give the difference between the two times as the decimal fraction of a 24 hr day. In Numbers, "date&time" cells include the date. The TIMEVALUE function converts them to a decimal fraction of a day, which effectively removes the date, then subtracts them.

The function given above fails if the end time is not in the same day as the start time (such as 11:59PM today to 1:00AM tomorrow). The Excel function fails, too.

If you want the date to be taken into consideration, this would be the formula:

=FLOOR(DUR2DAYS(D18−C18)*1440,6)/60+IF(MOD(DUR2DAYS(D18−C18),1)<>0,0.1,0)

You must ensure you also include the date when entering the times. The default is "today" if you enter time only. I would recommend you format the cell to show the date as well as the time.

If you format columns C and D as text, you can enter the "time" without a date. It will be text but Numbers will figure it out. Either formula will work. It will have the same problem regarding times that cross a date boundary.

Scott_Tripp Said:

"need help with Numbers formula."

-------

Ask this in Microsoft Forums.

That forum is designed specifically for questions like this.

Use this: The Microsoft Excel Community - Microsoft

Scott_Tripp Said:

"need help with Numbers formula.: I am not really sure I follow you. Why would I ask a question in an Excel group for help with a Numbers formula?“

———-

Excel-to-Numbers:

Because your basing you post on Excel-created formulas. Then using those to get transformed formulas. Compatibility is the issue here.

See if this does it:

=ROUNDUP(DUR2DAYS(D−C)*24,1)

I assume all time entries are whole minutes, no seconds. Any seconds will round up. For instance 6 min 3 sec would be the same result as 7 min

Did you mean to use "24h" in the MOD function instead of "12h"? As it is it zeros out every 12 hours.

As an alternative to Badunit's excellent solution, you could also use:

=CEILING(DUR2HOURS(MOD(D2−C2,"12h")),0.1)

Possible advantages:

-- It will give you a meaningful result if occasionally you fly past midnight into another day (though not if the duration of the flight exceeds 24 hours).

-- It uses the vaguely aeronautical CEILING function.😀

The formula doesn't care if your cells in C and D are formatted as Date & Time or Text. Date & Time may be easier for data input on the iPad. I use Text on the Mac so I don't have to worry about entering the date in the same cell.

SG

Badunit wrote:

Did you mean to use "24h" in the MOD function instead of "12h"? As it is it zeros out every 12 hours.

Yes, you're right.

=CEILING(DUR2HOURS(MOD(D2−C2,"24h")),0.1)

SG