Time comparison : problem of day induced

Question

Time comparison : problem of day induced

Hi all,

Comparison between times is sometime tricky.

I just learnt comparing 2 different hours that the day is merged with time, but without showing.

So that in fact I'm not comparing hr : min but day hr: min where day is not expressed (unless you double-click in the cell to find out), as in the examples below coming from the same table :

In BL2, the value should turn red because after time in E2 : it does not because time in E2 was entered today, and BL2 comes from a computed cell and copy-pasted which includes a different day (actually 1/1/1904, that is well before E2)

Rather than BL5 and BM6 are typed value the same day than col E & F : and then the conditionnal rule works.

Does anyone know how to get rid of the day value when comparing times ?.

Thanks all.

Lionnel

iMac Line (2012 and Later)

Posted on Aug 19, 2019 4:12 PM

Reply

Answer 1

Top-ranking reply

Barry

Level 9

60,084 points

Aug 19, 2019 5:12 PM in response to lionnelfromparis

Hi Lionnel,

In Numbers, ALL dates or times (of day) are Date and Time values marking a specific instant in time.

If they are entered values, these 'rules' apply:

Date only entered: Numbers automatically sets the time part to 00:00:00 (midnight, at the start of that date).

Time only entered: Numbers automatically sets the Date part to the date on which the entry was made.

Date and time entered: Numbers records the specific time on the specific day, as entered.

You can extract a TIMEVALUE from a Date and Time value. The TIMEVALUE function returns a decimal fraction representing the fraction of a 24 hour day.

You can use the DURATION function to convert the TIMEVALUE result to a Duration, expressed in hour and minutes.

Here are three examples,all using the 'times' entered in B2 and C2.

B2 was entered 'yesterday', so the date part of that Date and Time is August 18, 2019.

2 was entered 'today', so the date art of that Date and Time value is August 19, 2019.

Each cell in column D contains the formula shown to its right.

Note the comma at the beginning of the 'contents' of DURATION. This tells DURATION that the number (0.175) represents 'days'.

You can learn more about DURATION in the Function browser.

Regards,

Barry

Reply

Answer 2

SGIII

Level 8

37,654 points

Aug 20, 2019 10:02 AM in response to lionnelfromparis

To avoid the problem of different underlying dates that aren't shown,I usually just enter times as text. Either format the cells explicitly as text or, if the format is left at Automatic, type an apostrophe (a ' ) followed by the time.

Numbers is smart enough to parse that string (text) and do date-time arithmetic on it.

SG

Reply

Answer 3

Aug 20, 2019 8:29 AM in response to Barry

Hello Barry,

Thanks for your answer.

SG gave me also an answer on my other thread "Converting decimal time to hr:min format", but still it does not do the job completely with my numbers since the reference day in the formula I have is 1/1/1904, not today.

So here is what I want to do, since there are few tens of shedules that are given to me in decimal, and I don't intend to do the conversion by hand each time; the pdf below is an illustration :

If you have any idea of how to get this working ...

Thanks for your attention.

Lionnel

Reply

Answer 4

Aug 20, 2019 10:36 AM in response to SGIII

Yessss !!!.

I changed format in Table 2 C & D for Text instead of Time.

The only thing is that the 2 arguments I compare must be written the same : if I use "h" to separate hours and minutes in the timetable, i must also use "h" in the formula results. With "h" on one hand and " : " on the other hand comparison does not work.

Great, thanks a lot. !!

Lionnel.

Reply