[program-l] Re: Excel....
- From: "Corbett, James" <James.Corbett@xxxxxxxxxxxxx>
- To: <program-l@xxxxxxxxxxxxx>
- Date: Tue, 24 Mar 2009 10:23:26 -0400
Travis:
What I would like to do is type something like 8 - 4 into a single cell and it
would calculate and display 8. However the fly in the ointment would be 8:10 -
4:35 and so on.... I can live with typing 8:25 but lately I've been lazy as
sin and well you know.
Many thanks all.
J.
-----Original Message-----
From: program-l-bounce@xxxxxxxxxxxxx [mailto:program-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Travis Roth
Sent: March 24, 2009 10:18
To: program-l@xxxxxxxxxxxxx
Subject: [program-l] Re: Excel....
What format do you want the data?
You could try string manipulation functions to extract the two parts. If the
format is always consistent just using left() and right() with the number of
characters may work. If it varies though, then find() would come into play and
the complexity appears to go up.
VBA may be easier.
Its doable of course, but Excel seems to be designed for one value per cell.
-----Original Message-----
From: program-l-bounce@xxxxxxxxxxxxx [mailto:program-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Corbett, James
Sent: Tuesday, March 24, 2009 8:25 AM
To: program-l@xxxxxxxxxxxxx
Subject: [program-l] Re: Excel....
Travis:
I would really like to have the start and end values in a single cell, any
ideas?
Jim
-----Original Message-----
From: program-l-bounce@xxxxxxxxxxxxx [mailto:program-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Travis Roth
Sent: March 24, 2009 09:02
To: program-l@xxxxxxxxxxxxx
Subject: [program-l] Re: Excel....
Hi Jim,
According to http://www.cpearson.com/excel/datearith.htm
It says:
You can determine the number of hours and minutes between two times by
subtracting the two times. However, since Excel cannot handle negative times,
you must use an =IF statement to adjust the time accordingly. If your times
were entered without a date (e.g, 22:30), the following statement will compute
the interval between two times in A1 and B1 .
=IF(A1>B1,B1+1-A1,B1-A1)
-----Original Message-----
From: program-l-bounce@xxxxxxxxxxxxx [mailto:program-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Corbett, James
Sent: Tuesday, March 24, 2009 7:17 AM
To: program-l@xxxxxxxxxxxxx
Subject: [program-l] Excel....
Ok, yet another question on this evolving project....
Lets say in cell A1 thru E1 I sum the value as previously suggested and
subtract 37.5 in order to display the number of straight hours of OT worked....
Ok, works nice but now I want to be able to in a single cell enter the start
and end time, and calculate the actual amount of hours worked in that day....
How would I go about this?
Jim
James M. Corbett
A / Technical Specialist
GST/HST Micros | Micros de la TPS/TVH
GST/HST Redesign Division | Division de la restructuration de la TPS/TVH
Revenue and Accounting Systems Directorate (RASD) | Direction des Systèmes de
revenu et de comptabilité (DSRC) Information Technology Branch (ITB) |
Direction générale de l'informatique
(DGI)
Canada Revenue Agency | Agence du revenue du Canada
(613) 941-1338
"...Is a hippopotamus a hippopotamus, or just a really cool Opotamus?"
** To leave the list, click on the immediately-following link:-
** [mailto:program-l-request@xxxxxxxxxxxxx?subject=unsubscribe]
** If this link doesn't work then send a message to:
** program-l-request@xxxxxxxxxxxxx
** and in the Subject line type
** unsubscribe
** For other list commands such as vacation mode, click on the
** immediately-following link:-
** [mailto:program-l-request@xxxxxxxxxxxxx?subject=faq]
** or send a message, to
** program-l-request@xxxxxxxxxxxxx with the Subject:- faq
** To leave the list, click on the immediately-following link:-
** [mailto:program-l-request@xxxxxxxxxxxxx?subject=unsubscribe]
** If this link doesn't work then send a message to:
** program-l-request@xxxxxxxxxxxxx
** and in the Subject line type
** unsubscribe
** For other list commands such as vacation mode, click on the
** immediately-following link:-
** [mailto:program-l-request@xxxxxxxxxxxxx?subject=faq]
** or send a message, to
** program-l-request@xxxxxxxxxxxxx with the Subject:- faq
** To leave the list, click on the immediately-following link:-
** [mailto:program-l-request@xxxxxxxxxxxxx?subject=unsubscribe]
** If this link doesn't work then send a message to:
** program-l-request@xxxxxxxxxxxxx
** and in the Subject line type
** unsubscribe
** For other list commands such as vacation mode, click on the
** immediately-following link:-
** [mailto:program-l-request@xxxxxxxxxxxxx?subject=faq]
** or send a message, to
** program-l-request@xxxxxxxxxxxxx with the Subject:- faq
** To leave the list, click on the immediately-following link:-
** [mailto:program-l-request@xxxxxxxxxxxxx?subject=unsubscribe]
** If this link doesn't work then send a message to:
** program-l-request@xxxxxxxxxxxxx
** and in the Subject line type
** unsubscribe
** For other list commands such as vacation mode, click on the
** immediately-following link:-
** [mailto:program-l-request@xxxxxxxxxxxxx?subject=faq]
** or send a message, to
** program-l-request@xxxxxxxxxxxxx with the Subject:- faq
** To leave the list, click on the immediately-following link:-
** [mailto:program-l-request@xxxxxxxxxxxxx?subject=unsubscribe]
** If this link doesn't work then send a message to:
** program-l-request@xxxxxxxxxxxxx
** and in the Subject line type
** unsubscribe
** For other list commands such as vacation mode, click on the
** immediately-following link:-
** [mailto:program-l-request@xxxxxxxxxxxxx?subject=faq]
** or send a message, to
** program-l-request@xxxxxxxxxxxxx with the Subject:- faq
Other related posts:
