Time and text calculations

jdubbs41

New Member
Joined
Jul 2, 2015
Messages
8
I am trying to build a weekly time sheet with in and out times, as well as "vacation" and "off" text in the cells, but when I sum everything up, I get a value error. Is there a way to use a function in addition to the sum function to tell excel the text is either zero or a number?
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Chrisdontm

Well-known Member
Joined
May 4, 2015
Messages
810
An example would help...

Off the cuff I would say to have the text in a separate column next to the time...

And if necessary concatenate them in the following column...
 

jdubbs41

New Member
Joined
Jul 2, 2015
Messages
8
I have attached a portion of the timesheet, and I hope you can help with this


Sun Mon Tues Wed Thurs Fri Sat Totals
IN OUT IN OUT IN OUT IN OUT IN OUT IN OUT IN OUT
JEFF off off vac vac vac vac 700 1500 #VALUE!
NESSA 7:00 15:00 7:00 19:00 7:00 19:00 8:00 16:00 7:00 19:00 8:00 16:00 off off 56.50

As you can see, I have recorded in and out times, but if I include the cell with "vac" or "off", I get an error. Any help will be appreciated. Thanks
 
Last edited:

Chrisdontm

Well-known Member
Joined
May 4, 2015
Messages
810
Oh I see what you mean...

Well, I can't say that this is the best answer, but it will get you there...

If SUNDAY starts in column " B " and SATURDAY ends in column " O " and the total is in column " P "...

P6 would look like this;

=SUM((N(C6)-N(B6))*24,(N(E6)-N(D6))*24,(N(G6)-N(F6))*24,(N(I6)-N(H6))*24,(N(K6)-N(J6))*24,(N(M6)-N(L6))*24,(N(O6)-N(N6)*24))

Of course you will have to minus out for lunches...

The " N " function will count a text as zero, but it will leave it alone if it is a number...

I think you could make it work from here... ☺
 

jdubbs41

New Member
Joined
Jul 2, 2015
Messages
8

ADVERTISEMENT

Okay, I am going to push the envelope. Is there a way to calculate within a cell and total it at the end of the cells? i.e. if cell c4, there is 0700-1900, and cell d5, there is 07:00-19:00. How would I add the hours from those two cells, and make it total 24 hours at the end? I hope it is possible. Thank you for your help in advance.
 

Chrisdontm

Well-known Member
Joined
May 4, 2015
Messages
810
Ok, that was tricky, but I have an answer for you...
but,
It works off of the values being setup the same way...
Since you are using Military time you don't have to put the colons within the stated time, ( which works easier anyway )
You have colons in the D5 time but not in the C4 time...

Don't use colons in your time and this ought to work;

=NUMBERVALUE(RIGHT(C4,4),".")/100-NUMBERVALUE(LEFT(C4,4),".")/100+NUMBERVALUE(RIGHT(D5,4),".")/100-NUMBERVALUE(LEFT(D5,4),".")/100
 

jdubbs41

New Member
Joined
Jul 2, 2015
Messages
8

ADVERTISEMENT

Okay, I can see how this would work, but is this at the end of the row used for the totals? Also, if I wanted to put text into the cells, and have the calculations show these as zero, would I need to do a separate statement? i.e. if I wanted to put the word "off" or "vac" in the cel, how would this be done?
 

jdubbs41

New Member
Joined
Jul 2, 2015
Messages
8
WEEK 1 130 MAPUNAPUNA WORK SED

Below is an example of what I have in my cells, and I can use in and out times, but I would like to know if I can do it with this type of schedule, and have totals at the end, while treating the text as zeros. Any ideas? thank you for the help.



JULY 19 20 21 22 23 24 25
Sun Monday tues Wed Thurs Fri SAT HOURS
JEFF off off vac vac 700-300 off off off 8
NESSA 700-300 700-1900 1000-1900 700-1900 1000-1900 1000-1900 off 46
WILLY off 900-1800 900-1800 900-1800 900-1800 900-1800 off
MIA 730-230 off 630-630 off 630-630 630-630 off 36
MIKE off 600-230 600-230 600-230 600-230 600-230 off 40
PHILIP 800-200 830-500 off off off off 800-200 off off 20
REGGIE off 800-500 off 800-500 800-500 800-500 800-200 40
KENNY off 11-500 11-500 11-500 off 830-500 800-200 30
JOHN W. off float 4th 730-4 730-4 730-4 730-4 off 40
BILL F. off 700-330 700-330 700-330 off off 24
 

Chrisdontm

Well-known Member
Joined
May 4, 2015
Messages
810
That long calculation was for one day's time, so we would have to have that 7-times longer within the totals square...

Since you are the one creating the time sheet I would suggest making it easier to manage...

You could add in another column per day, or another row... (a skinny row under the other row perhaps)... lightly shading ever other person's row to help differentiate...

We could keep enlarging formulas, but if you had to work on it for any reason it would be a terror...

Another desirable feature to separating the "in and out" times into their own square is that you could have a drop down menu in every square of the possible times. ( When doing that, you can have VAC and OFF as options in the drop down as well...)

Then using the first formula setup we discussed...

One other thought; Do a search for " Excel Time Sheet Templates " you will find many options...
 

Watch MrExcel Video

Forum statistics

Threads
1,122,819
Messages
5,598,289
Members
414,223
Latest member
Accountant2B

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top