help with formula

**szurkeshadow** · 10-17-2012, 01:55 PM

this is for a time sheet with 6.5 and 8.5 hour shifts
now if A1 is 10:00:00am and A2 is 6:30:00pm i need B1 to be =A1+TIME(2,0,0) but if A1 is 10:00:00am and A2 is only 4:30:00pm i need B1 to be blank.
Please if anyone knows this help

**jake.masters** · 10-17-2012, 01:58 PM

Try:

=IF(A2="4:00.00pm", "", A1+TIME(2,0,0)

Or maybe have a control cell with 4pm time, and say =IF(A2="ControlCellLocation", "", A1+TIME(2,0,0)

**jake.masters** · 10-17-2012, 02:03 PM

However, your limitation here is the trigger for the IF is exactly 4pm. If the person clocks in a 4:01 or 3:59, this will not work. It depends if it is entered (controlled) or live data.

**szurkeshadow** · 10-17-2012, 02:06 PM

sorry i should have been clearer the times will change as its a rotational roster its the time variance of 6.5 or 8.5 that remain the constant. but i do thank you for your quick response

**jake.masters** · 10-17-2012, 02:09 PM

If that was the case, then I would have a calculation column for the hours worked, and set this to be =IF(CalcColumn="6.5", "", A1+TIME(2,0,0).

Excel calculates dates in terms of days, so to get hours, subtract the information then multiply by 24 to convert to days. You could leave it in terms of days, but be aware of that if you expect to see 6.5 and you see .27

**jake.masters** · 10-17-2012, 02:18 PM

If you posted a sample sheet, it may help us figure out this a little faster.

**arlu1201** · 10-17-2012, 02:36 PM

szurkeshadow,

Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem. Once you have done this please send me a PM and I will remove this request.

To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

**szurkeshadow** · 10-17-2012, 02:42 PM

this is a rough out of it

time sheet 2.jpg

**jake.masters** · 10-17-2012, 02:46 PM

If you attach the spreadsheet I'd be more likely to help you out. No offense, but I don't want to retype all of that data. Could you just send the .xlsx?

**FDibbins** · 10-17-2012, 02:49 PM

szurkesshadow and jake.masters...

Your post does not comply with Rule 7 of our Forum RULES. Please do not ignore Moderators' or Administrators' requests - note that this includes requests by senior members as well, if you are unclear about their request or instruction then send a private message to them asking for help. Do not post a reply to a thread where a moderator has requested an action that has not been complied with e.g Title change or Code tags...etc