If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Time Keeping
I'm trying to make a time sheet for myself but my time keeps coming up
negative. in cell a1 i have 9:00 AM (with the cell formated as time h:mm PM) and cell b1 5:00 PM (with the cell formated as time h:mm PM) and cell c1 formated as number (with formula (A1-B1)*24 . I'm getting 8 hours but it is coming out negative. so my cells look like this 9:00 AM 5:00 PM -8.00 What am i doing wrong? How can i get a positive number? |
#2
|
|||
|
|||
Time Keeping
=(B1-A1)*24
-- Kind regards, Niek Otten "Timeless Excel" Timeless wrote in message ... I'm trying to make a time sheet for myself but my time keeps coming up negative. in cell a1 i have 9:00 AM (with the cell formated as time h:mm PM) and cell b1 5:00 PM (with the cell formated as time h:mm PM) and cell c1 formated as number (with formula (A1-B1)*24 . I'm getting 8 hours but it is coming out negative. so my cells look like this 9:00 AM 5:00 PM -8.00 What am i doing wrong? How can i get a positive number? |
#3
|
|||
|
|||
Time Keeping
Subtract the start time from the end time, rather than the other way around:
=(b1-a1)*24. "Timeless Excel" wrote: I'm trying to make a time sheet for myself but my time keeps coming up negative. in cell a1 i have 9:00 AM (with the cell formated as time h:mm PM) and cell b1 5:00 PM (with the cell formated as time h:mm PM) and cell c1 formated as number (with formula (A1-B1)*24 . I'm getting 8 hours but it is coming out negative. so my cells look like this 9:00 AM 5:00 PM -8.00 What am i doing wrong? How can i get a positive number? |
#4
|
|||
|
|||
Time Keeping
Hi _____,
Actually if a time interval went through midnight you would get a negative number even with the "correct" formula (end-start). So you would want to add 24 hours (= 1 day) in the calculation. =((B1-A1)+(A1B1))*24 format as a number (not as time), or as =(B1-A1)+(A1B1) format as h:mm (as an Excel time, [h]:mm for a total) The A1B1 is a logical it will return 0 or 1 Excel dates and times are the number of days (and fractional days) pas a reference date. For more information on Date and Time see http://www.mvps.org/dmcritchie/excel/datetime.htm http://www.cpearson.com/excel/datetime.htm -- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Niek Otten" wrote in message ... =(B1-A1)*24 -- Kind regards, Niek Otten "Timeless Excel" Timeless wrote in message ... I'm trying to make a time sheet for myself but my time keeps coming up negative. in cell a1 i have 9:00 AM (with the cell formated as time h:mm PM) and cell b1 5:00 PM (with the cell formated as time h:mm PM) and cell c1 formated as number (with formula (A1-B1)*24 . I'm getting 8 hours but it is coming out negative. so my cells look like this 9:00 AM 5:00 PM -8.00 What am i doing wrong? How can i get a positive number? |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Time Zone suggestions for Outlook Calendar | Ben Knox | Calendar | 2 | October 20th, 2005 03:42 PM |
Please Help! Ref Article ID 210504 | Telobamipada | New Users | 8 | May 9th, 2005 01:09 PM |
Outlook 2003 Terminal Server Time Zone issue | Robert Strom | Calendar | 2 | May 26th, 2004 10:50 PM |
time zone & apt times auto changing | Mary | Calendar | 6 | May 25th, 2004 05:44 AM |
keeping time in excel | Mike | Setting up and Configuration | 2 | October 29th, 2003 03:28 PM |