2

Customized Excel Time Sheet For Employees

In the image attached, I need employees to manually enter login and logout times in the format (HH:MM AM/PM) only. And In the column E I need a formula to calculate hours and minutes of duration in between.

I have tried with =text(D2-C2,"h:mm") formula and when I applied the same to all the columns, they're updating with existing empty cells and resulting 00:00 in the E column.

I only need to update column E only when C and D column values are updated.

Can someone please help me in this regard? I need time frame as 12Hour format.

braX
  • 11,506
  • 5
  • 20
  • 33
Mansi Rao
  • 44
  • 6
  • You should consider controlling their input : if they make a typing error such as "92:0" instead of "9:20" - data validation can do this. – Solar Mike Feb 01 '18 at 07:42

2 Answers2

1

You can use =IF(AND(C1<>"",D1<>""),D1-C1,"") in your No Of Hours column to calculate only if Log In Time and Log Out Time are filled.

For formatting, you can use Custom Format - h:mm for the No Of Hours column.

  • Thank You Arul. But I am unable to find "custom format" as you mentioned, as I am working on Google Sheets(Online). – Mansi Rao Feb 02 '18 at 04:22
  • See if [this](https://stackoverflow.com/questions/25710299/how-to-format-a-duration-as-hhmm-in-the-new-google-sheets) helps. –  Feb 02 '18 at 05:18
1

1, Try not to convert real time to text.

  1. Use this formula in F2 then fill down. =E2-D2+AND(E2<D2, E2>0) This should also work if the shift spans midnight.

  2. Use this custom number format hh:mm_);;; on the cells.

Example:

enter image description here

  • Hello Heeped.. There's a problem with the answer, as it calculates the value even if the values are not provided. – Mansi Rao Feb 02 '18 at 04:24
  • 1
    You write an IF with AND to make sure there are both values. –  Feb 02 '18 at 04:45