Software
I'm trying to make my work schedule on Excel and I want to add in a line such as:
9:00a-2:00p. Can excel add something in the same line? Can you put =SUM(A1+A1) or something like that?
If not, and i'm sure you can't, is there any suggestions for me?
by sacf14
If you select any cell you can try this:
-right click on the cell
-Format cell option
-"number" Tab
-Select the last option: "defined by user"
-Type this code: "9:00a-2:00p" ####
Done. You can add a formula and have everything in the same cell. The problem is that you have to enter a different code for every cell, eg: "3:00p-5:00p" ####
I hope this helps...
by dr
Hi andy,
If you are using the 12 hour clock (format cell as AM/PM) then you would want to use two columns for the times and a third to do the math:
Col A = 9 a
Col B = 2 p
Col C has formula
9:00 a 2:00 p =((B1-A1)+(A1>B1))*24
the first () in the formula subtracts the times for a total of elapsed hours. The second accounts for times that span midnight ie: 10 pm to 3 am and multiply by 24 gives you the number of hours.
Cheers,
dr
by cnwoods
If you format the column or cells as time (h:mm or h:mm AM/PM are both listed under custom formats), you can do the sums with them without needing to worry about the conversion to 60/24.
Looks like you're doing some kind of timesheet. I'd suggest one column for start, one for finish, and if you like you can add either a single one for lunch duration or two for lunch start & finish.
Formulas for these respectively would be:
With lunch duration
A1=start, B1=lunch duration, C1=finish
Total hrs formula would be "=(C1-A1)+B1" (without the quote marks)
With lunch start & finish
A1=start, B1=lunch start, C1=lunch end, D1=finish time
Total hrs formula would be "=(B1-A1)+(D1-C1)"
So long as you've formatted all the columns with the time format, and entered them in that format (using the colon) it should work.
This discussion is closed to new replies.
Sort by Trending