Time formats...

**neilcarden** · 09-04-2007, 07:20 AM

Hi,

Ok something is really bugging me. I'm setting a format in a cell to hh:mm, but when I click in that cell it shows as 'dd/mm/yyyy hh:mm' in the formula bar. No matter which format I change it to, it always shows that. However in other spreadsheets it shows as hh:mm.

Is there a setting or something I've not ticked or ticked?

Thanks

**Special-K** · 09-04-2007, 07:35 AM

If its a small file post it here

**oldchippy** · 09-04-2007, 07:37 AM

Originally Posted by neilcarden

Hi,

Ok something is really bugging me. I'm setting a format in a cell to hh:mm, but when I click in that cell it shows as 'dd/mm/yyyy hh:mm' in the formula bar. No matter which format I change it to, it always shows that. However in other spreadsheets it shows as hh:mm.

Is there a setting or something I've not ticked or ticked?

Thanks

What value are you actually entering into the cell?

**neilcarden** · 09-04-2007, 07:45 AM

OK, see attached. Basically in each available cell, I just want to input hh:mm as seconds are not needed.
I hope I'm making sense.

**oldchippy** · 09-04-2007, 08:00 AM

In your cell you have 210 hours, as there are only 24 hours in a day, Excel is converting anything over 24 hours into days, starting from 01/01/1900. That is why you see 09/01/1900 18:00:00 - the equivalant of 8.75 days or 210 hours.

See this link

http://www.cpearson.com/excel/datetime.htm

**neilcarden** · 09-04-2007, 08:07 AM

Oh I see?

But is there any way of typing in 210 hours as 210:00 and it showing as that? Cos everything I try alters the way it shows?

**oldchippy** · 09-04-2007, 08:11 AM

Why not change the format to Number (two decimal places) and type in 210?

**neilcarden** · 09-04-2007, 08:35 AM

Yeah I thought of that, but we run into issues when it comes to 30 mins being .5 and then 15 mins and 10 mins - turning into correct fractions. I'd like it all in time format so it's consistent.