Changing a Date to a Number

  • Thread starter Thread starter Cheryl
  • Start date Start date
C

Cheryl

Okay.

If I am adding a timesheet. So I have In and Out columns.

IN OUT IN OUT TOTAL
9AM 12 1PM 4PM

The times are formatted as time. The total hours is
formatted at time. How can I change it to a number so
that I can add overtime to it later.

Thanks
 
Cheryl,

I'm not sure what exactly you have formatted. If you mean 9AM is formatted
as 9:00 , all you have to do is "reformat" it (or a copy of it) as general.
The outcome of this will be 0.375 (meaning 0.375 * 24 hours is 9 hours).
This would however need 1PM to be entered as 13:00 rather than 1:00

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
Hi
several ways:
- you can simply add the othertime to this value. e.g.
=A1+TIME(1,0,0)
to add one our

or if you want the hours in decimals from cell A1 try
=A1*24
and format this target cell as number
 
I can't have people type 1pm at 13:00. Is there any other
way?

Perhaps I can just not use times at all and use general
throughout the entire sheet.

Thanks for helping me.
 
Cheryl,

I think you misunderstood (or I phrased not clearly).

What I meant is if you want to "translate" the time to a number you'll have
to input 13:00 rather then 1:00 or 1PM)

Excel calculates time as a fraction of 24 hours : so 9:00 is (as fas as
Excel is concerned) 9/24 = 0.375.

If you input 1:00 Excel will see it as 1/24 = 0.041666.. ( = 1:00 midnight).

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
Hi Cheryl

If you want people to type in the times as 9AM, 12, 1PM, 4PM then you
are going to need the following rather complex formula:

=((IF(RIGHT(B1,2)="AM",LEFT(B1,FIND("A",B1)-1)/24,IF(RIGHT(B1,2)="PM",
(LEFT(B1,FIND("P",B1)-1)+12)/24,B1/24)))-(IF(RIGHT(A1,2)="AM",LEFT(A1,
FIND("A",A1)-1)/24,IF(RIGHT(A1,2)="PM",(LEFT(A1,FIND("P",A1)-1)+12)/24
,A1/24))))+((IF(RIGHT(D1,2)="AM",LEFT(D1,FIND("A",D1)-1)/24,IF(RIGHT(D
1,2)="PM",(LEFT(D1,FIND("P",D1)-1)+12)/24,D1/24)))-(IF(RIGHT(C1,2)="AM
",LEFT(C1,FIND("A",C1)-1)/24,IF(RIGHT(C1,2)="PM",(LEFT(C1,FIND("P",C1)
-1)+12)/24,C1/24))))
Format as Time

And that doesn't cover the possibilities of times spanning Midnight or
an end time of Midnight.

I think that proper entry of time might be best. You could control
that entry by using data validated drop downs.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top